C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
1
--  
================================================================================================================================  
======  
--ENZYME44KFJ19_ADVENTHEALTH_ALTAMONTE_SPRINGS  
--  
================================================================================================================================  
======  
1. Display all patients  
Concept: SELECT, FROM  
SELECT *  
FROM tblPatient_Infomation;  
2. Display selected patient columns  
Concept: SELECT  
SELECT  
Patient_ID,  
First_Name,  
Last_Name,  
Gender,  
Blood_Group,  
City  
FROM tblPatient_Infomation;  
3. Display unique blood groups  
Concept: DISTINCT  
SELECT DISTINCT Blood_Group  
FROM tblPatient_Infomation;  
4. Display first 10 patients  
Concept: TOP  
SELECT TOP 10  
Patient_ID,  
First_Name,  
Last_Name,  
Gender  
FROM tblPatient_Infomation;  
5. Find male patients  
Concept: WHERE, comparison  
SELECT  
Patient_ID,  
First_Name,  
Last_Name,  
Gender  
FROM tblPatient_Infomation  
WHERE Gender = 'MALE';  
6. Find female patients from Seattle  
Concept: WHERE, AND  
SELECT  
Patient_ID,  
First_Name,  
Last_Name,  
City  
FROM tblPatient_Infomation  
WHERE Gender = 'FEMALE'  
AND City = 'Seattle';  
7. Find patients from Seattle or Las Vegas  
Concept: OR  
SELECT  
Patient_ID,  
First_Name,  
Last_Name,  
City  
FROM tblPatient_Infomation  
WHERE City = 'Seattle'  
OR City = 'Las Vegas';  
8. Find patients who are NOT male  
Concept: NOT  
SELECT  
Patient_ID,  
First_Name,  
Last_Name,  
Gender  
FROM tblPatient_Infomation  
WHERE NOT Gender = 'MALE';  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
2
9. Find patients born between two dates  
Concept: BETWEEN  
SELECT  
Patient_ID,  
First_Name,  
Last_Name,  
Date_of_Birth  
FROM tblPatient_Infomation  
WHERE Date_of_Birth BETWEEN '1970-01-01' AND '1990-12-31';  
10. Find patients having selected blood groups  
Concept: IN  
SELECT  
Patient_ID,  
First_Name,  
Last_Name,  
Blood_Group  
FROM tblPatient_Infomation  
WHERE Blood_Group IN ('A+', 'B+', 'O+');  
11. Find patients who do not have selected blood groups  
Concept: NOT IN  
SELECT  
Patient_ID,  
First_Name,  
Last_Name,  
Blood_Group  
FROM tblPatient_Infomation  
WHERE Blood_Group NOT IN ('A+', 'B+');  
12. Find patients whose first name starts with H  
Concept: LIKE  
SELECT  
Patient_ID,  
First_Name,  
Last_Name  
FROM tblPatient_Infomation  
WHERE First_Name LIKE 'H%';  
13. Find patients whose first name contains "an"  
Concept: LIKE  
SELECT  
Patient_ID,  
First_Name,  
Last_Name  
FROM tblPatient_Infomation  
WHERE First_Name LIKE '%an%';  
14. Find patients whose occupation does NOT contain "Doctor"  
Concept: NOT LIKE  
SELECT  
Patient_ID,  
First_Name,  
Last_Name,  
Occupation  
FROM tblPatient_Infomation  
WHERE Occupation NOT LIKE '%Doctor%';  
15. Find patients with a non-null email  
Concept: IS NOT NULL  
SELECT  
Patient_ID,  
First_Name,  
Last_Name,  
Email  
FROM tblPatient_Infomation  
WHERE Email IS NOT NULL;  
16. Count total patients  
Concept: Aggregate function  
SELECT COUNT(*) AS Total_Patients  
FROM tblPatient_Infomation;  
Find average consultation fee  
Concept: AVG  
SELECT  
AVG(Consultancy_Fee) AS Average_Consultancy_Fee  
FROM tblMainMaster_Advent_health;  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
3
18. Find highest and lowest consultation fee  
Concept: MAX, MIN  
SELECT  
MAX(Consultancy_Fee) AS Highest_Fee,  
MIN(Consultancy_Fee) AS Lowest_Fee  
FROM tblMainMaster_Advent_health;  
19. Count patients by gender  
Concept: GROUP BY, COUNT  
SELECT  
Gender,  
COUNT(*) AS Patient_Count  
FROM tblPatient_Infomation  
GROUP BY Gender;  
20. Count patients by blood group and show largest groups first  
Concept: GROUP BY, ORDER BY  
SELECT  
Blood_Group,  
COUNT(*) AS Patient_Count  
FROM tblPatient_Infomation  
GROUP BY Blood_Group  
ORDER BY Patient_Count DESC;  
21. Patient + clinical information  
Concept: INNER JOIN  
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
P.Gender,  
M.Cardiologist,  
M.Chief_Complaint,  
M.Risk_Factors  
FROM tblPatient_Infomation AS P  
INNER JOIN tblMainMaster_Advent_health AS M  
ON P.Patient_ID = M.Patient_ID;  
22. Find patients treated by Dr. Patel  
Concept: JOIN + WHERE  
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
M.Cardiologist,  
M.Chief_Complaint  
FROM tblPatient_Infomation AS P  
INNER JOIN tblMainMaster_Advent_health AS M  
ON P.Patient_ID = M.Patient_ID  
WHERE M.Cardiologist = 'Dr. Patel';  
23. Patients with chest pain and smoking risk  
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
M.Chief_Complaint,  
M.Risk_Factors  
FROM tblPatient_Infomation AS P  
INNER JOIN tblMainMaster_Advent_health AS M  
ON P.Patient_ID = M.Patient_ID  
WHERE M.Chief_Complaint = 'Chest Pain'  
AND M.Risk_Factors = 'Smoking';  
24. Patients with hypertension OR diabetes  
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
M.Medical_History  
FROM tblPatient_Infomation AS P  
INNER JOIN tblMainMaster_Advent_health AS M  
ON P.Patient_ID = M.Patient_ID  
WHERE M.Medical_History IN ('Hypertension', 'Diabetes');  
25. Number of patients handled by each cardiologist  
SELECT  
Cardiologist,  
COUNT(*) AS Patient_Count  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
4
FROM tblMainMaster_Advent_health  
GROUP BY Cardiologist  
ORDER BY Patient_Count DESC;  
26. Average consultancy fee by cardiologist  
SELECT  
Cardiologist,  
AVG(Consultancy_Fee) AS Average_Fee  
FROM tblMainMaster_Advent_health  
GROUP BY Cardiologist  
ORDER BY Average_Fee DESC;  
27. Cardiologists having more than 100 patients  
This is your first important HAVING.  
SELECT  
Cardiologist,  
COUNT(*) AS Patient_Count  
FROM tblMainMaster_Advent_health  
GROUP BY Cardiologist  
HAVING COUNT(*) > 100  
ORDER BY Patient_Count DESC;  
28. Procedures costing more than 5,000  
SELECT  
Procedure_ID,  
Patient_ID,  
Procedure_Name,  
Procedure_Charges,  
Procedure_Status  
FROM tblProcedure_Scans  
WHERE Procedure_Charges > 5000  
ORDER BY Procedure_Charges DESC;  
29. Emergency procedures with high charges  
SELECT  
Procedure_ID,  
Patient_ID,  
Procedure_Name,  
Visit_Type,  
Procedure_Charges  
FROM tblProcedure_Scans  
WHERE Visit_Type = 'Emergency'  
AND Procedure_Charges > 5000  
ORDER BY Procedure_Charges DESC;  
30. Number of procedures by department  
SELECT  
Department,  
COUNT(*) AS Procedure_Count  
FROM tblProcedure_Scans  
GROUP BY Department  
ORDER BY Procedure_Count DESC;  
31. Departments having more than 100 procedures  
SELECT  
Department,  
COUNT(*) AS Procedure_Count  
FROM tblProcedure_Scans  
GROUP BY Department  
HAVING COUNT(*) > 100;  
32. Average procedure charge by department  
SELECT  
Department,  
AVG(Procedure_Charges) AS Average_Charge  
FROM tblProcedure_Scans  
GROUP BY Department  
ORDER BY Average_Charge DESC;  
33. Find completed high-priority procedures  
SELECT  
Procedure_ID,  
Patient_ID,  
Procedure_Name,  
Priority,  
Procedure_Status  
FROM tblProcedure_Scans  
WHERE Priority IN ('High', 'Critical')  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
AND Procedure_Status = 'Completed';  
5
34. Find patients with finalized reports  
SELECT  
Patient_ID,  
Procedure_ID,  
Report_Status,  
Findings,  
Severity_Score  
FROM tblReports_tests  
WHERE Report_Status = 'Finalized';  
35. Find severe reports  
SELECT  
Patient_ID,  
Procedure_ID,  
Findings,  
Severity_Score  
FROM tblReports_tests  
WHERE Severity_Score >= 50  
ORDER BY Severity_Score DESC;  
36. Count reports by status  
SELECT  
Report_Status,  
COUNT(*) AS Report_Count  
FROM tblReports_tests  
GROUP BY Report_Status  
ORDER BY Report_Count DESC;  
37. Calculate total billing amount  
SELECT  
SUM(Total_Amount) AS Total_Billing  
FROM tblBilling_Summary;  
38. Calculate total paid and pending amounts  
SELECT  
SUM(Paid_Amount) AS Total_Paid,  
SUM(Pending_Amount) AS Total_Pending  
FROM tblBilling_Summary;  
39. Billing summary by payment method  
SELECT  
Payment_Method,  
COUNT(*) AS Transaction_Count,  
SUM(Total_Amount) AS Total_Billed,  
SUM(Paid_Amount) AS Total_Paid  
FROM tblBilling_Summary  
GROUP BY Payment_Method  
ORDER BY Total_Billed DESC;  
40. Patients with procedure + billing information  
Now were joining three tables.  
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
PR.Procedure_Name,  
PR.Procedure_Charges,  
B.Total_Amount,  
B.Paid_Amount,  
B.Pending_Amount  
FROM tblPatient_Infomation AS P  
INNER JOIN tblProcedure_Scans AS PR  
ON P.Patient_ID = PR.Patient_ID  
INNER JOIN tblBilling_Summary AS B  
ON PR.Procedure_ID = B.Procedure_ID;  
41. Patients whose procedure charge is above the average procedure charge  
Concept: Subquery + AVG  
SELECT  
Procedure_ID,  
Patient_ID,  
Procedure_Name,  
Procedure_Charges  
FROM tblProcedure_Scans  
WHERE Procedure_Charges >  
(
SELECT AVG(Procedure_Charges)  
FROM tblProcedure_Scans  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
6
)
ORDER BY Procedure_Charges DESC;  
42. Patients whose consultancy fee is above average  
SELECT  
Patient_ID,  
Cardiologist,  
Consultancy_Fee  
FROM tblMainMaster_Advent_health  
WHERE Consultancy_Fee >  
(
SELECT AVG(Consultancy_Fee)  
FROM tblMainMaster_Advent_health  
);  
43. Doctors whose average consultation fee is above the overall average  
SELECT  
Cardiologist,  
AVG(Consultancy_Fee) AS Average_Fee  
FROM tblMainMaster_Advent_health  
GROUP BY Cardiologist  
HAVING AVG(Consultancy_Fee) >  
(
SELECT AVG(Consultancy_Fee)  
FROM tblMainMaster_Advent_health  
)
ORDER BY Average_Fee DESC;  
44. Patients who have at least one procedure  
Concept: EXISTS  
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name  
FROM tblPatient_Infomation AS P  
WHERE EXISTS  
(
SELECT 1  
FROM tblProcedure_Scans AS PR  
WHERE PR.Patient_ID = P.Patient_ID  
);  
45. Patients who have NO procedures  
Concept: NOT EXISTS  
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name  
FROM tblPatient_Infomation AS P  
WHERE NOT EXISTS  
(
SELECT 1  
FROM tblProcedure_Scans AS PR  
WHERE PR.Patient_ID = P.Patient_ID  
);  
46. Patients who have both procedures and billing records  
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name  
FROM tblPatient_Infomation AS P  
WHERE EXISTS  
(
SELECT 1  
FROM tblProcedure_Scans AS PR  
WHERE PR.Patient_ID = P.Patient_ID  
)
AND EXISTS  
(
SELECT 1  
FROM tblBilling_Summary AS B  
WHERE B.Patient_ID = P.Patient_ID  
);  
47. Patients with procedures but no billing  
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
7
FROM tblPatient_Infomation AS P  
WHERE EXISTS  
(
SELECT 1  
FROM tblProcedure_Scans AS PR  
WHERE PR.Patient_ID = P.Patient_ID  
)
AND NOT EXISTS  
(
SELECT 1  
FROM tblBilling_Summary AS B  
WHERE B.Patient_ID = P.Patient_ID  
);  
48. Patients with finalized reports and severity above average  
SELECT  
R.Patient_ID,  
R.Procedure_ID,  
R.Findings,  
R.Severity_Score  
FROM tblReports_tests AS R  
WHERE R.Report_Status = 'Finalized'  
AND R.Severity_Score >  
(
SELECT AVG(Severity_Score)  
FROM tblReports_tests  
);  
49. Find the most expensive procedure  
SELECT  
Procedure_ID,  
Patient_ID,  
Procedure_Name,  
Procedure_Charges  
FROM tblProcedure_Scans  
WHERE Procedure_Charges =  
(
SELECT MAX(Procedure_Charges)  
FROM tblProcedure_Scans  
);  
50. Find all procedures costing the same as the most expensive procedure  
This looks similar to #49, but teaches you why we use a subquery instead of assuming one row.  
SELECT  
Procedure_ID,  
Patient_ID,  
Procedure_Name,  
Procedure_Charges  
FROM tblProcedure_Scans  
WHERE Procedure_Charges =  
(
SELECT MAX(Procedure_Charges)  
FROM tblProcedure_Scans  
);  
51. Find doctors whose consultation fee is greater than ANY consultation fee of doctors with 10+ years experience  
This introduces ANY.  
SELECT  
Doctor_ID ,  
Doctor_Name ,  
Experience_Years ,  
Consultation_Fee  
FROM tblDoctor_Information  
WHERE Consultation_Fee >  
ANY  
(
SELECT Consultation_Fee  
FROM tblDoctor_Information  
WHERE Experience_Years >= 10  
);  
52. Find doctors whose consultation fee is greater than ALL doctors with less than 10 years experience  
SELECT  
Doctor_ID ,  
Doctor_Name ,  
Experience_Years ,  
Consultation_Fee  
FROM tblDoctor_Information  
WHERE Consultation_Fee >  
ALL  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
8
(
SELECT Consultation_Fee  
FROM tblDoctor_Information  
WHERE Experience_Years < 10  
);  
53. Find doctors whose consultation fee is greater than SOME experienced doctors fee  
SOME is equivalent to ANY.  
SELECT  
Doctor_ID ,  
Doctor_Name ,  
Consultation_Fee  
FROM tblDoctor_Information  
WHERE Consultation_Fee >  
SOME  
(
SELECT Consultation_Fee  
FROM tblDoctor_Information  
WHERE Experience_Years >= 10  
);  
54. Patient + doctor + clinical information  
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
P.Primary_Doctor,  
D.Specialization,  
D.Experience_Years ,  
M.Chief_Complaint,  
M.Risk_Factors  
FROM tblPatient_Infomation AS P  
INNER JOIN tblMainMaster_Advent_health AS M  
ON P.Patient_ID = M.Patient_ID  
LEFT JOIN tblDoctor_Information AS D  
ON P.Primary_Doctor = D.Doctor_Name ;  
55. Count procedures per patient  
SELECT  
Patient_ID ,  
COUNT(*) AS Procedure_Count  
FROM tblProcedure_Scans  
GROUP BY Patient_ID  
ORDER BY Procedure_Count DESC;  
56. Find patients having more than one procedure  
SELECT  
Patient_ID ,  
COUNT(*) AS Procedure_Count  
FROM tblProcedure_Scans  
GROUP BY Patient_ID  
HAVING COUNT(*) > 1  
ORDER BY Procedure_Count DESC;  
57. Find patients whose total procedure charges exceed 10,000  
SELECT  
Patient_ID ,  
SUM(Procedure_Charges) AS Total_Procedure_Charges  
FROM tblProcedure_Scans  
GROUP BY Patient_ID  
HAVING SUM(Procedure_Charges) > 10000  
ORDER BY Total_Procedure_Charges DESC;  
58. Find patients whose total pending billing exceeds 10,000  
SELECT  
Patient_ID ,  
SUM(Pending_Amount) AS Total_Pending  
FROM tblBilling_Summary  
GROUP BY Patient_ID  
HAVING SUM(Pending_Amount) > 10000  
ORDER BY Total_Pending DESC;  
59. Find patients with procedure charges AND pending billing  
This is a realistic analytical query.  
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
9
PR.Total_Procedure_Charges,  
B.Total_Pending  
FROM tblPatient_Infomation AS P  
INNER JOIN  
(
SELECT  
Patient_ID,  
SUM(Procedure_Charges) AS Total_Procedure_Charges  
FROM tblProcedure_Scans  
GROUP BY Patient_ID  
) AS PR  
ON P.Patient_ID = PR.Patient_ID  
INNER JOIN  
(
SELECT  
Patient_ID,  
SUM(Pending_Amount) AS Total_Pending  
FROM tblBilling_Summary  
GROUP BY Patient_ID  
) AS B  
ON P.Patient_ID = B.Patient_ID  
WHERE PR.Total_Procedure_Charges > 10000  
AND B.Total_Pending > 10000;  
60. FINAL CHALLENGE — Patient clinical + procedure + report + billing  
This combines almost everything learned so far.  
Requirement:  
Find patients who:  
1. Have a clinical record.  
2. Have a procedure.  
3. Have a finalized report.  
4. Have procedure charges above the average procedure charge.  
5. Have a pending billing amount greater than 0.  
6. Display patient, doctor, procedure, report and billing information.  
7. Sort by highest pending amount.  
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
M.Cardiologist,  
M.Chief_Complaint,  
PR.Procedure_Name,  
PR.Procedure_Charges,  
R.Report_Status,  
R.Findings,  
R.Severity_Score,  
B.Total_Amount,  
B.Paid_Amount,  
B.Pending_Amount  
FROM tblPatient_Infomation AS P  
INNER JOIN tblMainMaster_Advent_health AS M  
ON P.Patient_ID = M.Patient_ID  
INNER JOIN tblProcedure_Scans AS PR  
ON P.Patient_ID = PR.Patient_ID  
INNER JOIN tblReports_tests AS R  
ON PR.Procedure_ID = R.Procedure_ID  
INNER JOIN tblBilling_Summary AS B  
ON PR.Procedure_ID = B.Procedure_ID  
WHERE R.Report_Status = 'Finalized'  
AND PR.Procedure_Charges >  
(
SELECT AVG(Procedure_Charges)  
FROM tblProcedure_Scans  
)
AND B.Pending_Amount > 0  
ORDER BY B.Pending_Amount DESC;  
----------------------------------------------------------------------------------------------------------------------------------  
-------------------------------------------------------------------------------------------------------------  
LEVEL — SUBQUERIES  
1. Find patients whose age is above the average age  
Find  
Find all patients whose age is greater than the average age of all patients.  
SELECT  
Patient_ID,  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
10  
First_Name,  
Last_Name,  
Age  
FROM tblPatient_Infomation  
WHERE Age >  
(
SELECT AVG(Age)  
FROM tblPatient_Infomation  
)
ORDER BY Age DESC;  
2. Find patients from the same city as a particular patient  
Find  
Find all patients who live in the same city as the patient whose Patient_ID is 1005.  
SELECT  
Patient_ID,  
First_Name,  
Last_Name,  
City  
FROM tblPatient_Infomation  
WHERE City =  
(
SELECT City  
FROM tblPatient_Infomation  
WHERE Patient_ID = 1005  
)
AND Patient_ID <> 1005;  
3. Find procedures costing more than the average procedure charge  
Find  
Find all procedures whose charges are greater than the average procedure charge.  
SELECT  
Patient_ID,  
[Procedure_ID ],  
Procedure_Name,  
Procedure_Charges  
FROM tblProcedure_Scans  
WHERE Procedure_Charges >  
(
SELECT AVG(Procedure_Charges)  
FROM tblProcedure_Scans  
)
ORDER BY Procedure_Charges DESC;  
4. Find patients who have at least one procedure  
Find  
Find patients who have at least one procedure record.  
SELECT  
Patient_ID,  
First_Name,  
Last_Name  
FROM tblPatient_Infomation  
WHERE Patient_ID IN  
(
SELECT DISTINCT Patient_ID  
FROM tblProcedure_Scans  
);  
5. Find patients who have no procedure  
Find  
Find patients who do not have any procedure record.  
SELECT  
Patient_ID,  
First_Name,  
Last_Name  
FROM tblPatient_Infomation  
WHERE Patient_ID NOT IN  
(
SELECT DISTINCT Patient_ID  
FROM tblProcedure_Scans  
);  
6. Find patients whose procedure charge is above their own average  
Find  
Find procedures whose charge is greater than the average procedure charge of that same patient.  
SELECT  
P.Patient_ID,  
P.Procedure_Name,  
P.Procedure_Charges  
FROM tblProcedure_Scans AS P  
WHERE P.Procedure_Charges >  
(
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
11  
SELECT AVG(P2.Procedure_Charges)  
FROM tblProcedure_Scans AS P2  
WHERE P2.Patient_ID = P.Patient_ID  
)
ORDER BY  
P.Patient_ID,  
P.Procedure_Charges DESC;  
7. Find patients whose billing is above average billing  
Find  
Find patients whose total billing amount is greater than the average total billing amount per patient.  
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name  
FROM tblPatient_Infomation AS P  
WHERE  
(
SELECT SUM(B.Total_Amount)  
FROM tblBilling_Summary AS B  
WHERE B.Patient_ID = P.Patient_ID  
)
>
(
SELECT AVG(Patient_Total)  
FROM  
(
SELECT  
Patient_ID,  
SUM(Total_Amount) AS Patient_Total  
FROM tblBilling_Summary  
GROUP BY Patient_ID  
) AS X  
);  
8. Find patients having at least one expensive procedure  
Find  
Find patients who have at least one completed procedure costing more than 5,000.  
SELECT  
Patient_ID,  
First_Name,  
Last_Name  
FROM tblPatient_Infomation AS P  
WHERE EXISTS  
(
SELECT 1  
FROM tblProcedure_Scans AS PR  
WHERE PR.Patient_ID = P.Patient_ID  
AND PR.Procedure_Status = 'Completed'  
AND PR.Procedure_Charges > 5000  
);  
9. Find patients with no cancelled billing  
Find  
Find patients who have billing records but do not have any Cancelled billing record.  
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name  
FROM tblPatient_Infomation AS P  
WHERE EXISTS  
(
SELECT 1  
FROM tblBilling_Summary AS B  
WHERE B.Patient_ID = P.Patient_ID  
)
AND NOT EXISTS  
(
SELECT 1  
FROM tblBilling_Summary AS B2  
WHERE B2.Patient_ID = P.Patient_ID  
AND B2.Billing_Status = 'Cancelled'  
);  
10. Find patients whose highest procedure charge is above 10,000  
Find  
Find patients whose maximum procedure charge is greater than 10,000.  
SELECT  
P.Patient_ID,  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
12  
P.First_Name,  
P.Last_Name  
FROM tblPatient_Infomation AS P  
WHERE  
(
SELECT MAX(PR.Procedure_Charges)  
FROM tblProcedure_Scans AS PR  
WHERE PR.Patient_ID = P.Patient_ID  
) > 10000  
ORDER BY P.Patient_ID;  
11. Find patients whose procedure count is above average  
Find  
Find patients who have more procedures than the average number of procedures per patient.  
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
(
SELECT COUNT(*)  
FROM tblProcedure_Scans AS PR  
WHERE PR.Patient_ID = P.Patient_ID  
) AS Procedure_Count  
FROM tblPatient_Infomation AS P  
WHERE  
(
SELECT COUNT(*)  
FROM tblProcedure_Scans AS PR  
WHERE PR.Patient_ID = P.Patient_ID  
)
>
(
SELECT AVG(Procedure_Count)  
FROM  
(
SELECT  
Patient_ID,  
COUNT(*) AS Procedure_Count  
FROM tblProcedure_Scans  
GROUP BY Patient_ID  
) AS X  
)
ORDER BY Procedure_Count DESC;  
12. Find patients whose pending billing is above their own paid amount  
Find  
Find patients whose total pending billing is greater than their total paid amount.  
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name  
FROM tblPatient_Infomation AS P  
WHERE  
(
SELECT SUM(B.Pending_Amount)  
FROM tblBilling_Summary AS B  
WHERE B.Patient_ID = P.Patient_ID  
)
>
(
SELECT SUM(B2.Paid_Amount)  
FROM tblBilling_Summary AS B2  
WHERE B2.Patient_ID = P.Patient_ID  
);  
13. Find patients whose severity is above average severity  
Find  
Find patients whose maximum finalized-report severity is greater than the average maximum severity across patients.  
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
(
SELECT MAX(R.Severity_Score)  
FROM tblReports_tests AS R  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
13  
WHERE R.Patient_ID = P.Patient_ID  
AND R.Report_Status = 'Finalized'  
) AS Maximum_Severity  
FROM tblPatient_Infomation AS P  
WHERE  
(
SELECT MAX(R.Severity_Score)  
FROM tblReports_tests AS R  
WHERE R.Patient_ID = P.Patient_ID  
AND R.Report_Status = 'Finalized'  
)
>
(
SELECT AVG(Max_Severity)  
FROM  
(
SELECT  
Patient_ID,  
MAX(Severity_Score) AS Max_Severity  
FROM tblReports_tests  
WHERE Report_Status = 'Finalized'  
GROUP BY Patient_ID  
) AS X  
)
ORDER BY Maximum_Severity DESC;  
14. Find doctors treating above-average expensive patients  
Find  
Find doctors whose patients have an average procedure charge greater than the overall average procedure charge.  
SELECT  
M.Cardiologist,  
AVG(PR.Procedure_Charges) AS Doctor_Average_Charge  
FROM tblMainMaster_Advent_health AS M  
INNER JOIN tblProcedure_Scans AS PR  
ON M.Patient_ID = PR.Patient_ID  
GROUP BY  
M.Cardiologist  
HAVING AVG(PR.Procedure_Charges) >  
(
SELECT AVG(Procedure_Charges)  
FROM tblProcedure_Scans  
);  
15. Find patients with more procedures than their doctor’s average patient  
Find  
Find patients who have more procedures than the average number of procedures performed on patients belonging to the same doctor.  
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
M.Cardiologist,  
(
SELECT COUNT(*)  
FROM tblProcedure_Scans AS PR  
WHERE PR.Patient_ID = P.Patient_ID  
) AS Patient_Procedure_Count  
FROM tblPatient_Infomation AS P  
INNER JOIN tblMainMaster_Advent_health AS M  
ON P.Patient_ID = M.Patient_ID  
WHERE  
(
SELECT COUNT(*)  
FROM tblProcedure_Scans AS PR  
WHERE PR.Patient_ID = P.Patient_ID  
)
>
(
SELECT AVG(Patient_Procedure_Count)  
FROM  
(
SELECT  
M2.Cardiologist,  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
14  
M2.Patient_ID,  
COUNT(PR2.[Procedure_ID ]) AS Patient_Procedure_Count  
FROM tblMainMaster_Advent_health AS M2  
INNER JOIN tblProcedure_Scans AS PR2  
ON M2.Patient_ID = PR2.Patient_ID  
GROUP BY  
M2.Cardiologist,  
M2.Patient_ID  
) AS X  
WHERE X.Cardiologist = M.Cardiologist  
)
ORDER BY Patient_Procedure_Count DESC;  
16. Find patients whose total procedure charges are greater than every other patient in their city  
Find  
Find patients whose total completed-procedure charges are greater than the total completed-procedure charges of every other  
patient in the same city.  
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
P.City,  
(
SELECT SUM(PR.Procedure_Charges)  
FROM tblProcedure_Scans AS PR  
WHERE PR.Patient_ID = P.Patient_ID  
AND PR.Procedure_Status = 'Completed'  
) AS Patient_Total_Charges  
FROM tblPatient_Infomation AS P  
WHERE  
(
SELECT SUM(PR.Procedure_Charges)  
FROM tblProcedure_Scans AS PR  
WHERE PR.Patient_ID = P.Patient_ID  
AND PR.Procedure_Status = 'Completed'  
)
>= ALL  
(
SELECT  
SUM(PR2.Procedure_Charges)  
FROM tblPatient_Infomation AS P2  
INNER JOIN tblProcedure_Scans AS PR2  
ON P2.Patient_ID = PR2.Patient_ID  
WHERE P2.City = P.City  
AND P2.Patient_ID <> P.Patient_ID  
AND PR2.Procedure_Status = 'Completed'  
GROUP BY P2.Patient_ID  
)
ORDER BY Patient_Total_Charges DESC;  
17. Find patients whose procedure charge is greater than ALL procedures of another priority  
Find  
Find procedures whose charges are greater than every Low-priority procedure charge.  
SELECT  
Patient_ID,  
[Procedure_ID ],  
Procedure_Name,  
Priority,  
Procedure_Charges  
FROM tblProcedure_Scans AS PR  
WHERE PR.Procedure_Charges >  
ALL  
(
SELECT  
PR2.Procedure_Charges  
FROM tblProcedure_Scans AS PR2  
WHERE PR2.Priority = 'Low'  
)
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
15  
ORDER BY  
PR.Procedure_Charges DESC;  
18. Find patients who have every required condition  
Find  
Find patients who have at least two completed procedures, total procedure charges above 10,000, maximum finalized severity above  
70, total pending billing above 5,000, and whose pending billing is greater than their paid billing.  
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name  
FROM tblPatient_Infomation AS P  
WHERE  
-- At least 2 completed procedures  
(
SELECT COUNT(*)  
FROM tblProcedure_Scans AS PR  
WHERE PR.Patient_ID = P.Patient_ID  
AND PR.Procedure_Status = 'Completed'  
)
>= 2  
AND  
-- Procedure charges > 10,000  
(
SELECT SUM(PR.Procedure_Charges)  
FROM tblProcedure_Scans AS PR  
WHERE PR.Patient_ID = P.Patient_ID  
AND PR.Procedure_Status = 'Completed'  
)
> 10000  
AND  
-- Maximum severity > 70  
(
SELECT MAX(R.Severity_Score)  
FROM tblReports_tests AS R  
WHERE R.Patient_ID = P.Patient_ID  
AND R.Report_Status = 'Finalized'  
)
> 70  
AND  
-- Pending > 5,000  
(
SELECT SUM(B.Pending_Amount)  
FROM tblBilling_Summary AS B  
WHERE B.Patient_ID = P.Patient_ID  
)
> 5000  
AND  
-- Pending > Paid  
(
SELECT SUM(B.Pending_Amount)  
FROM tblBilling_Summary AS B  
WHERE B.Patient_ID = P.Patient_ID  
)
>
(
SELECT SUM(B2.Paid_Amount)  
FROM tblBilling_Summary AS B2  
WHERE B2.Patient_ID = P.Patient_ID  
)
ORDER BY P.Patient_ID;  
19. Find patients whose total billing is greater than ALL patients treated by the same doctor  
Find  
Find patients whose total billing is greater than every other patient treated by the same doctor.  
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
M.Cardiologist,  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
16  
(
SELECT SUM(B.Total_Amount)  
FROM tblBilling_Summary AS B  
WHERE B.Patient_ID = P.Patient_ID  
) AS Patient_Total_Billing  
FROM tblPatient_Infomation AS P  
INNER JOIN tblMainMaster_Advent_health AS M  
ON P.Patient_ID = M.Patient_ID  
WHERE  
(
SELECT SUM(B.Total_Amount)  
FROM tblBilling_Summary AS B  
WHERE B.Patient_ID = P.Patient_ID  
)
>= ALL  
(
SELECT  
SUM(B2.Total_Amount)  
FROM tblMainMaster_Advent_health AS M2  
INNER JOIN tblBilling_Summary AS B2  
ON M2.Patient_ID = B2.Patient_ID  
WHERE M2.Cardiologist = M.Cardiologist  
AND M2.Patient_ID <> P.Patient_ID  
GROUP BY M2.Patient_ID  
)
ORDER BY Patient_Total_Billing DESC;  
Finding  
This finds the highest-billed patient under each doctor.  
________________________________________  
20.   MASTER SUBQUERY QUESTION  
Find  
Find the top 20 patients whose total completed-procedure charges are greater than the average patient procedure spending, whose  
maximum finalized report severity is greater than the average maximum severity, whose total pending billing is greater than the  
average patient pending billing, who have at least one High or Critical procedure, and who have no Cancelled billing record.  
Also ensure that their total procedure charges are greater than every patient treated by the same doctor.  
This combines almost everything weve learned.  
Query  
SELECT TOP 20  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
P.City,  
M.Cardiologist,  
-- Procedure total  
(
SELECT SUM(PR.Procedure_Charges)  
FROM tblProcedure_Scans AS PR  
WHERE PR.Patient_ID = P.Patient_ID  
AND PR.Procedure_Status = 'Completed'  
) AS Total_Procedure_Charges,  
-- Maximum severity  
(
SELECT MAX(R.Severity_Score)  
FROM tblReports_tests AS R  
WHERE R.Patient_ID = P.Patient_ID  
AND R.Report_Status = 'Finalized'  
) AS Maximum_Severity,  
-- Pending  
(
SELECT SUM(B.Pending_Amount)  
FROM tblBilling_Summary AS B  
WHERE B.Patient_ID = P.Patient_ID  
) AS Total_Pending  
FROM tblPatient_Infomation AS P  
INNER JOIN tblMainMaster_Advent_health AS M  
ON P.Patient_ID = M.Patient_ID  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
WHERE  
17  
/* =========================================================  
1. PROCEDURE TOTAL > AVERAGE PATIENT PROCEDURE TOTAL  
========================================================= */  
(
SELECT SUM(PR.Procedure_Charges)  
FROM tblProcedure_Scans AS PR  
WHERE PR.Patient_ID = P.Patient_ID  
AND PR.Procedure_Status = 'Completed'  
)
>
(
SELECT AVG(Patient_Procedure_Total)  
FROM  
(
SELECT  
Patient_ID,  
SUM(Procedure_Charges) AS Patient_Procedure_Total  
FROM tblProcedure_Scans  
WHERE Procedure_Status = 'Completed'  
GROUP BY Patient_ID  
) AS X  
)
/* =========================================================  
2. MAXIMUM SEVERITY > AVERAGE MAXIMUM SEVERITY  
========================================================= */  
AND  
(
SELECT MAX(R.Severity_Score)  
FROM tblReports_tests AS R  
WHERE R.Patient_ID = P.Patient_ID  
AND R.Report_Status = 'Finalized'  
)
>
(
SELECT AVG(Max_Severity)  
FROM  
(
SELECT  
Patient_ID,  
MAX(Severity_Score) AS Max_Severity  
FROM tblReports_tests  
WHERE Report_Status = 'Finalized'  
GROUP BY Patient_ID  
) AS X  
)
/* =========================================================  
3. PENDING > AVERAGE PATIENT PENDING  
========================================================= */  
AND  
(
SELECT SUM(B.Pending_Amount)  
FROM tblBilling_Summary AS B  
WHERE B.Patient_ID = P.Patient_ID  
)
>
(
SELECT AVG(Patient_Pending)  
FROM  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
18  
(
SELECT  
Patient_ID,  
SUM(Pending_Amount) AS Patient_Pending  
FROM tblBilling_Summary  
GROUP BY Patient_ID  
) AS X  
)
/* =========================================================  
4. AT LEAST ONE HIGH / CRITICAL PROCEDURE  
========================================================= */  
AND EXISTS  
(
SELECT 1  
FROM tblProcedure_Scans AS PR  
WHERE PR.Patient_ID = P.Patient_ID  
AND PR.Procedure_Status = 'Completed'  
AND PR.Priority IN  
(
'High',  
'Critical'  
)
)
/* =========================================================  
5. NO CANCELLED BILLING  
========================================================= */  
AND NOT EXISTS  
(
SELECT 1  
FROM tblBilling_Summary AS B  
WHERE B.Patient_ID = P.Patient_ID  
AND B.Billing_Status = 'Cancelled'  
)
/* =========================================================  
6. PATIENT TOTAL > EVERY PATIENT UNDER SAME DOCTOR  
========================================================= */  
AND  
(
SELECT SUM(PR.Procedure_Charges)  
FROM tblProcedure_Scans AS PR  
WHERE PR.Patient_ID = P.Patient_ID  
AND PR.Procedure_Status = 'Completed'  
)
>= ALL  
(
SELECT  
SUM(PR2.Procedure_Charges)  
FROM tblMainMaster_Advent_health AS M2  
INNER JOIN tblProcedure_Scans AS PR2  
ON M2.Patient_ID = PR2.Patient_ID  
WHERE M2.Cardiologist = M.Cardiologist  
AND M2.Patient_ID <> P.Patient_ID  
AND PR2.Procedure_Status = 'Completed'  
GROUP BY M2.Patient_ID  
)
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
ORDER BY  
19  
Total_Procedure_Charges DESC,  
Maximum_Severity DESC,  
Total_Pending DESC;  
----------------------------------------------------------------------------------------------------------------------------------  
-------------------------------------------------------------------------------------  
1. TOP 20 patients with multiple filtering conditions  
Business Question  
Find the top 20 patients who:  
• are from Seattle or Las Vegas  
• are not Male  
• procedure charge is between 3,000 and 10,000  
• procedure is either Completed or Scheduled  
• sort by highest procedure charge.  
SQL  
SELECT TOP 20  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
P.City,  
P.Gender,  
PR.Procedure_Name,  
PR.Procedure_Charges,  
PR.Procedure_Status  
FROM tblPatient_Infomation AS P  
INNER JOIN tblProcedure_Scans AS PR  
ON P.Patient_ID = PR.Patient_ID  
WHERE P.City IN ('Seattle', 'Las Vegas')  
AND P.Gender <> 'MALE'  
AND PR.Procedure_Charges BETWEEN 3000 AND 10000  
AND PR.Procedure_Status IN ('Completed', 'Scheduled')  
ORDER BY PR.Procedure_Charges DESC;  
2. Patient demographic filtering with AND + OR + NOT  
Business Question  
Find patients who:  
• have blood group A+, B+, or O+  
• are not students  
• first name starts with A or S  
• email is available  
• city is not Chicago  
• occupation contains either Engineer or Manager.  
SQL  
SELECT TOP 30  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
P.Gender,  
P.Blood_Group,  
P.City,  
P.Occupation  
FROM tblPatient_Infomation AS P  
WHERE P.Blood_Group IN ('A+', 'B+', 'O+')  
AND P.Occupation <> 'Student'  
AND (  
P.First_Name LIKE 'A%'  
OR P.First_Name LIKE 'S%'  
)
AND P.Email IS NOT NULL  
AND P.City NOT IN ('Chicago')  
AND (  
P.Occupation LIKE '%Engineer%'  
OR P.Occupation LIKE '%Manager%'  
)
ORDER BY P.First_Name, P.Last_Name;  
3. High-risk patients with expensive completed procedures  
Business Question  
Find patients who:  
• have Smoking OR Diabetes as a risk factor  
• have a completed procedure  
• procedure charge > 5,000.  
SQL  
SELECT  
P.Patient_ID,  
P.First_Name,  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
20  
P.Last_Name,  
M.Risk_Factors,  
PR.Procedure_Name,  
PR.Procedure_Charges,  
PR.Procedure_Status  
FROM tblPatient_Infomation AS P  
INNER JOIN tblMainMaster_Advent_health AS M  
ON P.Patient_ID = M.Patient_ID  
INNER JOIN tblProcedure_Scans AS PR  
ON P.Patient_ID = PR.Patient_ID  
WHERE  
(
M.Risk_Factors LIKE '%Smoking%'  
OR M.Risk_Factors LIKE '%Diabetes%'  
)
AND PR.Procedure_Status = 'Completed'  
AND PR.Procedure_Charges > 5000  
ORDER BY PR.Procedure_Charges DESC;  
4. Department performance analysis  
Business Question  
Find departments where:  
• procedure count > 100  
• average procedure charge > 4,000  
• maximum procedure charge > 6,500.  
SQL  
SELECT  
PR.Department,  
COUNT(*) AS Procedure_Count,  
AVG(PR.Procedure_Charges) AS Average_Charge,  
MAX(PR.Procedure_Charges) AS Maximum_Charge  
FROM tblProcedure_Scans AS PR  
GROUP BY PR.Department  
HAVING COUNT(*) > 100  
AND AVG(PR.Procedure_Charges) > 4000  
AND MAX(PR.Procedure_Charges) > 6500  
ORDER BY Average_Charge DESC;  
5. Cardiologists with high patient volume and high average fees  
Business Question  
Find cardiologists who:  
• handle more than 190 patients  
• have average consultancy fee > 1,100  
• maximum fee > 1,500.  
SQL  
SELECT  
M.Cardiologist,  
COUNT(*) AS Patient_Count,  
AVG(M.Consultancy_Fee) AS Average_Fee,  
MAX(M.Consultancy_Fee) AS Maximum_Fee  
FROM tblMainMaster_Advent_health AS M  
GROUP BY M.Cardiologist  
HAVING COUNT(*) > 190  
AND AVG(M.Consultancy_Fee) > 1100  
AND MAX(M.Consultancy_Fee) > 1500  
ORDER BY Average_Fee DESC;  
6. Patients who have NO procedure  
Now lets use NOT EXISTS.  
Business Question  
Find patients who:  
• have a clinical record  
• have no procedure  
• have an email address.  
SQL  
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
P.Email  
FROM tblPatient_Infomation AS P  
INNER JOIN tblMainMaster_Advent_health AS M  
ON P.Patient_ID = M.Patient_ID  
WHERE P.Email IS NOT NULL  
AND NOT EXISTS  
(
SELECT 1  
FROM tblProcedure_Scans AS PR  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
21  
WHERE PR.Patient_ID = P.Patient_ID  
);  
7. Find doctors that dont exist in doctor master  
This is a very good real-world data-quality query.  
Business Question  
Find cardiologists appearing in the clinical table who are missing from the doctor master table.  
SQL  
SELECT  
M.Cardiologist,  
COUNT(*) AS Patient_Count,  
AVG(M.Consultancy_Fee) AS Average_Fee  
FROM tblMainMaster_Advent_health AS M  
LEFT JOIN tblDoctor_Information AS D  
ON M.Cardiologist = D.Doctor_Name  
WHERE D.Doctor_ID IS NULL  
GROUP BY M.Cardiologist  
HAVING COUNT(*) > 10  
ORDER BY Patient_Count DESC;  
8. Billing analysis by payment method  
Business Question  
For each payment method calculate:  
• number of transactions  
• total billed  
• total paid  
• total pending  
and show methods with total billing > 10 million.  
SQL  
SELECT  
B.Payment_Method,  
COUNT(*) AS Transaction_Count,  
SUM(B.Total_Amount) AS Total_Billed,  
SUM(B.Paid_Amount) AS Total_Paid,  
SUM(B.Pending_Amount) AS Total_Pending  
FROM tblBilling_Summary AS B  
GROUP BY B.Payment_Method  
HAVING SUM(B.Total_Amount) > 10000000  
ORDER BY Total_Billed DESC;  
9. Billing status analysis  
Business Question  
Find billing statuses where:  
• there are more than 180 bills  
• pending amount > 300,000  
• total billed amount > 10 million.  
SQL  
SELECT  
B.Billing_Status,  
COUNT(*) AS Bill_Count,  
SUM(B.Total_Amount) AS Total_Billed,  
SUM(B.Pending_Amount) AS Total_Pending  
FROM tblBilling_Summary AS B  
GROUP BY B.Billing_Status  
HAVING COUNT(*) > 180  
AND SUM(B.Pending_Amount) > 300000  
AND SUM(B.Total_Amount) > 10000000  
ORDER BY Total_Pending DESC;  
----------------------------------------------------------------------------------------------------------------------------------  
--------------------------------------------------------------------------------------------------------------  
1. Completed expensive procedures  
Find all patients who had a procedure costing more than 5,000, where the procedure was completed and the priority was High.  
SELECT *  
FROM tblProcedure_Scans  
WHERE Procedure_Charges > 5000  
AND Procedure_Status = 'Completed'  
AND Priority = 'High';  
2. Female patients with selected blood groups  
Find all female patients whose blood group is A+, B+, or O+ and whose email address is available.  
SELECT *  
FROM tblPatient_Infomation  
WHERE Gender = 'FEMALE'  
AND Blood_Group IN ('A+', 'B+', 'O+')  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
AND Email IS NOT NULL;  
22  
3. High consultancy fee  
Find all patients whose consultancy fee is between 1,000 and 5,000, whose appointment type is not Emergency, and whose payment  
status is Paid.  
SELECT *  
FROM tblMainMaster_Advent_health  
WHERE Consultancy_Fee BETWEEN 1000 AND 5000  
AND Appointment_Type <> 'Emergency'  
AND Payment_Status = 'Paid';  
4. Finalized severe reports  
Find all reports where the report is Finalized, the severity score is greater than 50, and pathology was detected.  
SELECT *  
FROM tblReports_tests  
WHERE Report_Status = 'Finalized'  
AND Severity_Score > 50  
AND Pathology_Detected = 'Yes';  
5. High-value billing records  
Find all billing records where the total amount is greater than 20,000, the pending amount is greater than 5,000, and the billing  
status is not Closed.  
SELECT *  
FROM tblBilling_Summary  
WHERE Total_Amount > 20000  
AND Pending_Amount > 5000  
AND Billing_Status <> 'Closed';  
6. Seattle or Las Vegas patients  
Find all female patients from Seattle or Las Vegas whose blood group is A+ or B+.  
SELECT *  
FROM tblPatient_Infomation  
WHERE Gender = 'FEMALE'  
AND City IN ('Seattle', 'Las Vegas')  
AND Blood_Group IN ('A+', 'B+');  
7. Chest or heart patients  
Find all patients whose chief complaint is either Chest Pain or Heart Pain, where the risk factor is Smoking or Diabetes, and the  
consultancy fee is greater than 1,000.  
SELECT  
Patient_ID,  
Chief_Complaint,  
Risk_Factors,  
Consultancy_Fee  
FROM tblMainMaster_Advent_health  
WHERE Chief_Complaint IN ('Chest Pain', 'Heart Pain')  
AND Risk_Factors IN ('Smoking', 'Diabetes')  
AND Consultancy_Fee > 1000;  
8. Completed or scheduled procedures  
Find all procedures where the procedure status is Completed or Scheduled, the priority is High or Critical, and the procedure  
charge is greater than 5,000.  
SELECT *  
FROM tblProcedure_Scans  
WHERE Procedure_Status IN ('Completed', 'Scheduled')  
AND Priority IN ('High', 'Critical')  
AND Procedure_Charges > 5000;  
9. Multiple medical conditions  
Find all patients whose medical history contains Diabetes or Hypertension, whose risk factor is Smoking or Obesity, and whose  
consultancy fee is between 2,000 and 8,000.  
SELECT  
Patient_ID,  
Medical_History,  
Risk_Factors,  
Consultancy_Fee  
FROM tblMainMaster_Advent_health  
WHERE Medical_History IN ('Diabetes', 'Hypertension')  
AND Risk_Factors IN ('Smoking', 'Obesity')  
AND Consultancy_Fee BETWEEN 2000 AND 8000;  
10. Complex AND + OR using parentheses  
Find all patients where either the patient is Male and has blood group A+ or B+, or the patient is Female and has blood group O+  
or AB+, and the patient has an available email address.  
SELECT *  
FROM tblPatient_Infomation  
WHERE  
(
Gender = 'MALE'  
AND Blood_Group IN ('A+', 'B+')  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
23  
)
OR  
(
Gender = 'FEMALE'  
AND Blood_Group IN ('O+', 'AB+')  
)
AND Email IS NOT NULL;  
11. Patient + procedure  
Find all patients from Seattle or Las Vegas who had a completed procedure costing between 3,000 and 8,000.  
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
P.City,  
PR.Procedure_Name,  
PR.Procedure_Charges,  
PR.Procedure_Status  
FROM tblPatient_Infomation AS P  
INNER JOIN tblProcedure_Scans AS PR  
ON P.Patient_ID = PR.Patient_ID  
WHERE P.City IN ('Seattle', 'Las Vegas')  
AND PR.Procedure_Status = 'Completed'  
AND PR.Procedure_Charges BETWEEN 3000 AND 8000  
ORDER BY PR.Procedure_Charges DESC;  
12. Patient + clinical  
Find all female patients from Seattle or Chicago who have Diabetes or Hypertension in their medical history and whose consultancy  
fee is above 2,000.  
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
P.City,  
P.Gender,  
M.Medical_History,  
M.Consultancy_Fee  
FROM tblPatient_Infomation AS P  
INNER JOIN tblMainMaster_Advent_health AS M  
ON P.Patient_ID = M.Patient_ID  
WHERE P.Gender = 'FEMALE'  
AND P.City IN ('Seattle', 'Chicago')  
AND M.Medical_History IN ('Diabetes', 'Hypertension')  
AND M.Consultancy_Fee > 2000;  
13. Patient + procedure + report  
Find all patients who had a completed MRI or CT procedure for which the report was Finalized, pathology was detected, and severity  
score was greater than 50.  
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
PR.Procedure_Name,  
PR.Procedure_Status,  
R.Report_Status,  
R.Pathology_Detected,  
R.Severity_Score  
FROM tblPatient_Infomation AS P  
INNER JOIN tblProcedure_Scans AS PR  
ON P.Patient_ID = PR.Patient_ID  
INNER JOIN tblReports_tests AS R  
ON PR.Procedure_ID = R.Procedure_ID  
WHERE PR.Procedure_Name IN ('MRI', 'CT Scan')  
AND PR.Procedure_Status = 'Completed'  
AND R.Report_Status = 'Finalized'  
AND R.Pathology_Detected = 'Yes'  
AND R.Severity_Score > 50;  
14. Patient + procedure + billing  
Find all patients who had a procedure costing more than 5,000 and have a pending billing amount greater than 2,000.  
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
PR.Procedure_Name,  
PR.Procedure_Charges,  
B.Total_Amount,  
B.Paid_Amount,  
B.Pending_Amount  
FROM tblPatient_Infomation AS P  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
24  
INNER JOIN tblProcedure_Scans AS PR  
ON P.Patient_ID = PR.Patient_ID  
INNER JOIN tblBilling_Summary AS B  
ON PR.Procedure_ID = B.Procedure_ID  
WHERE PR.Procedure_Charges > 5000  
AND B.Pending_Amount > 2000;  
15. Doctor name filtering  
Find all patients whose cardiologists name starts with Dr. P or Dr. J, whose consultancy fee is greater than 1,000, and whose  
medical history is not NULL.  
SELECT  
Patient_ID,  
Cardiologist,  
Consultancy_Fee,  
Medical_History  
FROM tblMainMaster_Advent_health  
WHERE  
(
Cardiologist LIKE 'Dr. P%'  
OR Cardiologist LIKE 'Dr. J%'  
)
AND Consultancy_Fee > 1000  
AND Medical_History IS NOT NULL;  
16. Risk-factor filtering  
Find all clinical records where the risk factor contains Smoking or Diabetes, does not contain Obesity, and the consultancy fee is  
between 1,000 and 5,000.  
SELECT *  
FROM tblMainMaster_Advent_health  
WHERE  
(
Risk_Factors LIKE '%Smoking%'  
OR Risk_Factors LIKE '%Diabetes%'  
)
AND Risk_Factors NOT LIKE '%Obesity%'  
AND Consultancy_Fee BETWEEN 1000 AND 5000;  
17. Missing doctor information  
Find all patients where the cardiologist information is missing OR the medical history is missing, but the consultancy fee is  
greater than 1,000.  
SELECT *  
FROM tblMainMaster_Advent_health  
WHERE  
(
Cardiologist IS NULL  
OR Medical_History IS NULL  
)
AND Consultancy_Fee > 1000;  
18. Departments with high activity  
Find departments having more than 100 procedures, an average procedure charge greater than 4,000, and at least one procedure  
costing more than 6,500.  
SELECT  
Department,  
COUNT(*) AS Procedure_Count,  
AVG(Procedure_Charges) AS Average_Charge,  
MAX(Procedure_Charges) AS Maximum_Charge  
FROM tblProcedure_Scans  
GROUP BY Department  
HAVING COUNT(*) > 100  
AND AVG(Procedure_Charges) > 4000  
AND MAX(Procedure_Charges) > 6500  
ORDER BY Average_Charge DESC;  
19. Doctors with high patient volume  
Find cardiologists who treated more than 150 patients and whose average consultancy fee is greater than 1,000.  
SELECT  
Cardiologist,  
COUNT(*) AS Patient_Count,  
AVG(Consultancy_Fee) AS Average_Fee  
FROM tblMainMaster_Advent_health  
GROUP BY Cardiologist  
HAVING COUNT(*) > 150  
AND AVG(Consultancy_Fee) > 1000  
ORDER BY Patient_Count DESC;  
20. Patients with expensive procedures  
Find patients who had at least two procedures, whose total procedure charges exceed 10,000, and whose maximum individual procedure  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
25  
charge is greater than 6,000.  
SELECT  
Patient_ID,  
COUNT(*) AS Procedure_Count,  
SUM(Procedure_Charges) AS Total_Charges,  
MAX(Procedure_Charges) AS Maximum_Charge  
FROM tblProcedure_Scans  
GROUP BY Patient_ID  
HAVING COUNT(*) >= 2  
AND SUM(Procedure_Charges) > 10000  
AND MAX(Procedure_Charges) > 6000  
ORDER BY Total_Charges DESC;  
21. Find female patients from selected cities  
Question  
Find all female patients from Seattle or Las Vegas whose blood group is A+ or B+ and whose email address is available. Display the  
patient ID, name, city, blood group, and email.  
Query  
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
P.City,  
P.Blood_Group,  
P.Email  
FROM tblPatient_Infomation AS P  
WHERE P.Gender = 'FEMALE'  
AND P.City IN ('Seattle', 'Las Vegas')  
AND P.Blood_Group IN ('A+', 'B+')  
AND P.Email IS NOT NULL  
ORDER BY P.City, P.First_Name;  
22. Find patients treated by selected cardiologists  
Question  
Find all patients treated by Dr. Patel or Dr. Johnson whose consultancy fee is between 1,000 and 2,000 and whose risk factor is  
either Smoking or Diabetes.  
Query  
SELECT  
Patient_ID,  
Cardiologist,  
Chief_Complaint,  
Risk_Factors,  
Consultancy_Fee  
FROM tblMainMaster_Advent_health  
WHERE Cardiologist IN ('Dr. Patel', 'Dr. Johnson')  
AND Consultancy_Fee BETWEEN 1000 AND 2000  
AND Risk_Factors IN ('Smoking', 'Diabetes')  
ORDER BY Consultancy_Fee DESC;  
23. Find high-priority procedures  
Question  
Find all procedures that are either Completed or Scheduled, have High or Critical priority, and have procedure charges between  
3,000 and 6,500.  
Query  
SELECT  
[Procedure_ID ],  
[Patient_ID ],  
[Procedure_Name ],  
Department,  
Procedure_Status,  
Priority,  
Procedure_Charges  
FROM tblProcedure_Scans  
WHERE Procedure_Status IN ('Completed', 'Scheduled')  
AND Priority IN ('High', 'Critical')  
AND Procedure_Charges BETWEEN 3000 AND 6500  
ORDER BY Procedure_Charges DESC;  
24. Find finalized serious reports  
Question  
Find all reports that are Finalized and have a severity score between 50 and 100. Display the report ID, patient ID, findings, and  
severity score.  
Query  
SELECT  
[Report_ID ],  
[Patient_ID ],  
Findings,  
[Report_Status ],  
[Severity_Score ]  
FROM tblReports_tests  
WHERE [Report_Status ] = 'Finalized'  
AND [Severity_Score ] BETWEEN 50 AND 100  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
ORDER BY [Severity_Score ] DESC;  
26  
25. Find high-value unpaid/disputed bills  
Question  
Find billing records where the total amount is greater than 20,000, pending amount is greater than 5,000, and the billing status  
is not Closed.  
Query  
SELECT  
[Bill_ID ],  
[Patient_ID ],  
[Total_Amount ],  
[Paid_Amount ],  
Pending_Amount,  
Billing_Status  
FROM tblBilling_Summary  
WHERE [Total_Amount ] > 20000  
AND Pending_Amount > 5000  
AND Billing_Status <> 'Closed'  
ORDER BY Pending_Amount DESC;  
26. Find departments with high procedure activity  
Question  
Find departments having more than 130 procedures, an average procedure charge greater than 4,000, and a maximum procedure charge  
greater than 6,500.  
Query  
SELECT  
Department,  
COUNT(*) AS Procedure_Count,  
AVG(Procedure_Charges) AS Average_Charge,  
MAX(Procedure_Charges) AS Maximum_Charge  
FROM tblProcedure_Scans  
GROUP BY Department  
HAVING COUNT(*) > 130  
AND AVG(Procedure_Charges) > 4000  
AND MAX(Procedure_Charges) > 6500  
ORDER BY Average_Charge DESC;  
27. Find cardiologists with high patient volume  
Question  
Find cardiologists who have handled more than 190 patients and whose average consultancy fee is greater than 1,100. Display the  
patient count, average fee, and maximum fee.  
Query  
SELECT  
Cardiologist,  
COUNT(*) AS Patient_Count,  
AVG(Consultancy_Fee) AS Average_Fee,  
MAX(Consultancy_Fee) AS Maximum_Fee  
FROM tblMainMaster_Advent_health  
GROUP BY Cardiologist  
HAVING COUNT(*) > 190  
AND AVG(Consultancy_Fee) > 1100  
ORDER BY Patient_Count DESC;  
28. Find patients with multiple medical risk conditions  
Question  
Find all clinical records where the medical history is Diabetes or Hypertension, the risk factor is Smoking or Obesity, and the  
consultancy fee is between 1,000 and 2,000.  
Query  
SELECT  
Patient_ID,  
Medical_History,  
Risk_Factors,  
Consultancy_Fee  
FROM tblMainMaster_Advent_health  
WHERE Medical_History IN ('Diabetes', 'Hypertension')  
AND Risk_Factors IN ('Smoking', 'Obesity')  
AND Consultancy_Fee BETWEEN 1000 AND 2000  
ORDER BY Consultancy_Fee DESC;  
29. Find patients using multiple tables  
Question  
Find female patients from Seattle or Las Vegas who have Smoking or Diabetes as a risk factor and whose consultancy fee is greater  
than 1,200. Display patient and clinical information.  
Query  
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
P.City,  
P.Gender,  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
27  
M.Cardiologist,  
M.Risk_Factors,  
M.Consultancy_Fee  
FROM tblPatient_Infomation AS P  
INNER JOIN tblMainMaster_Advent_health AS M  
ON P.Patient_ID = M.Patient_ID  
WHERE P.Gender = 'FEMALE'  
AND P.City IN ('Seattle', 'Las Vegas')  
AND M.Risk_Factors IN ('Smoking', 'Diabetes')  
AND M.Consultancy_Fee > 1200  
ORDER BY M.Consultancy_Fee DESC;  
30. Find expensive procedures with outstanding bills  
Question  
Find procedures where the procedure charge is greater than 5,000, the procedure was Completed or Successful, and the associated  
billing record has more than 10,000 pending.  
Query  
SELECT  
PR.[Procedure_ID ],  
PR.[Patient_ID ],  
PR.[Procedure_Name ],  
PR.Procedure_Status,  
PR.Procedure_Charges,  
B.[Total_Amount ],  
B.[Paid_Amount ],  
B.Pending_Amount,  
B.Billing_Status  
FROM tblProcedure_Scans AS PR  
INNER JOIN tblBilling_Summary AS B  
ON PR.[Procedure_ID ] = B.[Procedure_ID ]  
WHERE PR.Procedure_Charges > 5000  
AND PR.Procedure_Status IN ('Completed', 'Successful')  
AND B.Pending_Amount > 10000  
ORDER BY B.Pending_Amount DESC;  
31. Find patients with finalized reports and serious findings  
Question  
Find reports where the status is Finalized, severity is greater than 70, and the findings are not null.  
Query  
SELECT  
[Report_ID ],  
[Patient_ID ],  
Findings,  
[Report_Status ],  
[Severity_Score ]  
FROM tblReports_tests  
WHERE [Report_Status ] = 'Finalized'  
AND [Severity_Score ] > 70  
AND Findings IS NOT NULL  
ORDER BY [Severity_Score ] DESC;  
32. Find procedures above the average charge  
Question  
Find all completed procedures having a procedure charge greater than the average procedure charge across all procedures and having  
High or Critical priority.  
Query  
SELECT  
[Procedure_ID ],  
[Patient_ID ],  
[Procedure_Name ],  
Procedure_Status,  
Priority,  
Procedure_Charges  
FROM tblProcedure_Scans  
WHERE Procedure_Status = 'Completed'  
AND Priority IN ('High', 'Critical')  
AND Procedure_Charges >  
(
SELECT AVG(Procedure_Charges)  
FROM tblProcedure_Scans  
)
ORDER BY Procedure_Charges DESC;  
33. Find patients who have no procedure  
Question  
Find patients who have a patient record and an email address but do not have any procedure record.  
Query  
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
28  
P.Email  
FROM tblPatient_Infomation AS P  
WHERE P.Email IS NOT NULL  
AND NOT EXISTS  
(
SELECT 1  
FROM tblProcedure_Scans AS PR  
WHERE PR.[Patient_ID ] = P.Patient_ID  
)
ORDER BY P.Patient_ID;  
34. Find procedures that have billing records  
Question  
Find all procedures that have a corresponding billing record, where the billing amount is greater than 50,000 and the payment  
status is Paid.  
Query  
SELECT  
PR.[Procedure_ID ],  
PR.[Patient_ID ],  
PR.[Procedure_Name ],  
PR.Procedure_Charges,  
B.[Total_Amount ],  
B.[Paid_Amount ],  
B.[Payment_Status ]  
FROM tblProcedure_Scans AS PR  
INNER JOIN tblBilling_Summary AS B  
ON PR.[Procedure_ID ] = B.[Procedure_ID ]  
WHERE B.[Total_Amount ] > 50000  
AND B.[Payment_Status ] = 'Paid'  
ORDER BY B.[Total_Amount ] DESC;  
35. Find payment methods with high revenue  
Question  
Find payment methods having more than 180 transactions, total billed amount greater than 10 million, and total pending amount  
greater than 300,000.  
Query  
SELECT  
Payment_Method,  
COUNT(*) AS Transaction_Count,  
SUM([Total_Amount ]) AS Total_Billed,  
SUM([Paid_Amount ]) AS Total_Paid,  
SUM(Pending_Amount) AS Total_Pending  
FROM tblBilling_Summary  
GROUP BY Payment_Method  
HAVING COUNT(*) > 180  
AND SUM([Total_Amount ]) > 10000000  
AND SUM(Pending_Amount) > 300000  
ORDER BY Total_Billed DESC;  
36. Find billing statuses with large outstanding balances  
Question  
Find billing statuses where the number of bills is greater than 190, total billed amount exceeds 10 million, and total pending  
amount exceeds 400,000.  
Query  
SELECT  
Billing_Status,  
COUNT(*) AS Bill_Count,  
SUM([Total_Amount ]) AS Total_Billed,  
SUM(Pending_Amount) AS Total_Pending  
FROM tblBilling_Summary  
GROUP BY Billing_Status  
HAVING COUNT(*) > 190  
AND SUM([Total_Amount ]) > 10000000  
AND SUM(Pending_Amount) > 400000  
ORDER BY Total_Pending DESC;  
37. Find doctors using ANY  
Question  
Find doctors whose consultation fee is greater than ANY doctor having at least 15 years of experience.  
Query  
SELECT  
[Doctor_ID ],  
[Doctor_Name ],  
[Experience_Years ],  
[Consultation_Fee ]  
FROM tblDoctor_Information  
WHERE [Consultation_Fee ] >  
ANY  
(
SELECT [Consultation_Fee ]  
FROM tblDoctor_Information  
WHERE [Experience_Years ] >= 15  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
);  
29  
38. Find doctors using ALL  
Question  
Find doctors having at least 10 years of experience whose consultation fee is greater than ALL doctors having less than 10 years  
of experience.  
Query  
SELECT  
[Doctor_ID ],  
[Doctor_Name ],  
[Experience_Years ],  
[Consultation_Fee ]  
FROM tblDoctor_Information  
WHERE [Experience_Years ] >= 10  
AND [Consultation_Fee ] >  
ALL  
(
SELECT [Consultation_Fee ]  
FROM tblDoctor_Information  
WHERE [Experience_Years ] < 10  
);  
39. Find patients with specific demographic AND clinical conditions  
Question  
Find the top 20 female patients from Seattle, Las Vegas, or Chicago who have an A+, B+, or O+ blood group, have Diabetes or  
Hypertension in their medical history, do not have Smoking as a risk factor, and have a consultancy fee greater than 1,000.  
Query  
SELECT TOP 20  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
P.City,  
P.Blood_Group,  
M.Medical_History,  
M.Risk_Factors,  
M.Consultancy_Fee  
FROM tblPatient_Infomation AS P  
INNER JOIN tblMainMaster_Advent_health AS M  
ON P.Patient_ID = M.Patient_ID  
WHERE P.Gender = 'FEMALE'  
AND P.City IN ('Seattle', 'Las Vegas', 'Chicago')  
AND P.Blood_Group IN ('A+', 'B+', 'O+')  
AND M.Medical_History IN ('Diabetes', 'Hypertension')  
AND M.Risk_Factors NOT LIKE '%Smoking%'  
AND M.Consultancy_Fee > 1000  
ORDER BY M.Consultancy_Fee DESC;  
40.   FULL MIXED QUERY  
Now lets make one much closer to the difficulty you are looking for.  
Question  
Find the top 20 procedures where the procedure was Completed or Successful, priority was High or Critical, procedure charges were  
greater than the average procedure charge, and the corresponding billing record has a total amount greater than 50,000 and  
pending amount greater than 5,000. Display the patient ID, procedure name, department, procedure charge, total bill, paid  
amount, pending amount, and billing status.  
Query  
SELECT TOP 20  
PR.[Patient_ID ],  
PR.[Procedure_ID ],  
PR.[Procedure_Name ],  
PR.Department,  
PR.Procedure_Status,  
PR.Priority,  
PR.Procedure_Charges,  
B.[Total_Amount ],  
B.[Paid_Amount ],  
B.Pending_Amount,  
B.Billing_Status  
FROM tblProcedure_Scans AS PR  
INNER JOIN tblBilling_Summary AS B  
ON PR.[Procedure_ID ] = B.[Procedure_ID ]  
WHERE PR.Procedure_Status IN ('Completed', 'Successful')  
AND PR.Priority IN ('High', 'Critical')  
AND PR.Procedure_Charges >  
(
SELECT AVG(Procedure_Charges)  
FROM tblProcedure_Scans  
)
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
30  
AND B.[Total_Amount ] > 50000  
AND B.Pending_Amount > 5000  
ORDER BY  
B.Pending_Amount DESC,  
PR.Procedure_Charges DESC;  
----------------------------------------------------------------------------------------------------------------------------------  
---------------------------------------------------------------------------------  
JOIN COMPLEX QUESTIONS  
________________________________________  
41. Patients with procedures, reports and billing  
Find  
Find the top 20 patients who have a completed procedure with High or Critical priority, a finalized report with severity greater  
than 50, and a billing record where the pending amount is greater than 5,000. Display the patient name, procedure, report  
severity and pending amount.  
Query  
SELECT TOP 20  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
PR.Procedure_Name,  
PR.Department,  
PR.Procedure_Status,  
PR.Priority,  
PR.Procedure_Charges,  
R.Report_Status,  
R.Severity_Score,  
B.Total_Amount,  
B.Pending_Amount,  
B.Billing_Status  
FROM tblPatient_Infomation AS P  
INNER JOIN tblMainMaster_Advent_health AS M  
ON P.Patient_ID = M.Patient_ID  
INNER JOIN tblProcedure_Scans AS PR  
ON P.Patient_ID = PR.Patient_ID  
INNER JOIN tblReports_tests AS R  
ON P.Patient_ID = R.Patient_ID  
INNER JOIN tblBilling_Summary AS B  
ON P.Patient_ID = B.Patient_ID  
WHERE PR.Procedure_Status = 'Completed'  
AND PR.Priority IN ('High', 'Critical')  
AND R.Report_Status = 'Finalized'  
AND R.Severity_Score > 50  
AND B.Pending_Amount > 5000  
ORDER BY  
B.Pending_Amount DESC,  
PR.Procedure_Charges DESC;  
42. High-risk patients with expensive treatment  
Find  
Find patients from Seattle, Las Vegas or Chicago who have Diabetes or Hypertension in their medical history, have a High/Critical  
priority procedure costing more than 5,000, and have a pending bill greater than 2,000.  
Query  
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
P.City,  
M.Medical_History,  
M.Risk_Factors,  
PR.Procedure_Name,  
PR.Priority,  
PR.Procedure_Charges,  
B.Pending_Amount  
FROM tblPatient_Infomation AS P  
INNER JOIN tblMainMaster_Advent_health AS M  
ON P.Patient_ID = M.Patient_ID  
INNER JOIN tblProcedure_Scans AS PR  
ON P.Patient_ID = PR.Patient_ID  
INNER JOIN tblBilling_Summary AS B  
ON P.Patient_ID = B.Patient_ID  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
31  
WHERE P.City IN ('Seattle', 'Las Vegas', 'Chicago')  
AND M.Medical_History IN ('Diabetes', 'Hypertension')  
AND PR.Priority IN ('High', 'Critical')  
AND PR.Procedure_Charges > 5000  
AND B.Pending_Amount > 2000  
ORDER BY B.Pending_Amount DESC;  
43. Severe finalized reports with expensive procedures  
Find  
Find patients who have a finalized report with a severity score above 70, whose procedure was completed, whose procedure charge  
was above 4,000, and whose billing status is not Closed.  
Query  
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
PR.Procedure_Name,  
PR.Procedure_Charges,  
R.Report_Status,  
R.Severity_Score,  
B.Billing_Status,  
B.Pending_Amount  
FROM tblPatient_Infomation AS P  
INNER JOIN tblProcedure_Scans AS PR  
ON P.Patient_ID = PR.Patient_ID  
INNER JOIN tblReports_tests AS R  
ON P.Patient_ID = R.Patient_ID  
INNER JOIN tblBilling_Summary AS B  
ON P.Patient_ID = B.Patient_ID  
WHERE R.Report_Status = 'Finalized'  
AND R.Severity_Score > 70  
AND PR.Procedure_Status = 'Completed'  
AND PR.Procedure_Charges > 4000  
AND B.Billing_Status <> 'Closed'  
ORDER BY R.Severity_Score DESC;  
Finding  
44. Female patients with specific doctors and procedures  
Find  
Find female patients from Seattle, Chicago or Las Vegas who were treated by Dr. Patel, Dr. Johnson or Dr. Carter, underwent a  
Completed or Scheduled procedure, and have a billing amount greater than 20,000.  
Query  
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
P.City,  
M.Cardiologist,  
PR.Procedure_Name,  
PR.Procedure_Status,  
PR.Procedure_Charges,  
B.Total_Amount,  
B.Pending_Amount  
FROM tblPatient_Infomation AS P  
INNER JOIN tblMainMaster_Advent_health AS M  
ON P.Patient_ID = M.Patient_ID  
INNER JOIN tblProcedure_Scans AS PR  
ON P.Patient_ID = PR.Patient_ID  
INNER JOIN tblBilling_Summary AS B  
ON P.Patient_ID = B.Patient_ID  
WHERE P.Gender = 'FEMALE'  
AND P.City IN ('Seattle', 'Chicago', 'Las Vegas')  
AND M.Cardiologist IN ('Dr. Patel', 'Dr. Johnson', 'Dr. Carter')  
AND PR.Procedure_Status IN ('Completed', 'Scheduled')  
AND B.Total_Amount > 20000  
ORDER BY B.Total_Amount DESC;  
45. Patients with missing clinical information  
Find  
Find patients who have a procedure and billing record but whose clinical record has either a missing medical history or missing  
cardiologist information. The procedure must have a High or Critical priority and the pending amount must exceed 1,000.  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
32  
Query  
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
M.Cardiologist,  
M.Medical_History,  
PR.Procedure_Name,  
PR.Priority,  
PR.Procedure_Charges,  
B.Pending_Amount  
FROM tblPatient_Infomation AS P  
INNER JOIN tblMainMaster_Advent_health AS M  
ON P.Patient_ID = M.Patient_ID  
INNER JOIN tblProcedure_Scans AS PR  
ON P.Patient_ID = PR.Patient_ID  
INNER JOIN tblBilling_Summary AS B  
ON P.Patient_ID = B.Patient_ID  
WHERE  
(
M.Medical_History IS NULL  
OR M.Cardiologist IS NULL  
)
AND PR.Priority IN ('High', 'Critical')  
AND B.Pending_Amount > 1000  
ORDER BY B.Pending_Amount DESC;  
46. Patients with MRI/CT and serious reports  
Find  
Find patients who underwent either an MRI or CT procedure, where the procedure was Completed, the report was Finalized, pathology  
was detected, and the associated bill has an outstanding amount.  
Query  
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
PR.Procedure_Name,  
PR.Department,  
PR.Procedure_Status,  
R.Report_Status,  
R.Pathology_Detected,  
R.Severity_Score,  
B.Total_Amount,  
B.Pending_Amount  
FROM tblPatient_Infomation AS P  
INNER JOIN tblProcedure_Scans AS PR  
ON P.Patient_ID = PR.Patient_ID  
INNER JOIN tblReports_tests AS R  
ON P.Patient_ID = R.Patient_ID  
INNER JOIN tblBilling_Summary AS B  
ON P.Patient_ID = B.Patient_ID  
WHERE PR.Procedure_Name IN ('MRI', 'CT Scan')  
AND PR.Procedure_Status = 'Completed'  
AND R.Report_Status = 'Finalized'  
AND R.Pathology_Detected = 'Yes'  
AND B.Pending_Amount > 0  
ORDER BY R.Severity_Score DESC;  
47. Doctors and their expensive procedures  
Find  
Find patients treated by doctors having at least 10 years of experience, where the patient underwent a procedure costing more than  
5,000 and has a billing amount greater than 30,000.  
Query  
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
D.Doctor_Name,  
D.Experience_Years,  
D.Consultation_Fee,  
PR.Procedure_Name,  
PR.Procedure_Charges,  
B.Total_Amount  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
FROM tblPatient_Infomation AS P  
33  
INNER JOIN tblMainMaster_Advent_health AS M  
ON P.Patient_ID = M.Patient_ID  
INNER JOIN tblDoctor_Information AS D  
ON M.Cardiologist = D.Doctor_Name  
INNER JOIN tblProcedure_Scans AS PR  
ON P.Patient_ID = PR.Patient_ID  
INNER JOIN tblBilling_Summary AS B  
ON P.Patient_ID = B.Patient_ID  
WHERE D.Experience_Years >= 10  
AND PR.Procedure_Charges > 5000  
AND B.Total_Amount > 30000  
ORDER BY  
D.Experience_Years DESC,  
B.Total_Amount DESC;  
48. Doctors with high fees and patients with serious reports  
Find  
Find patients treated by doctors whose consultation fee is greater than 1,500, where the patient has a finalized report with  
severity above 60 and a completed procedure costing more than 4,000.  
Query  
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
D.Doctor_Name,  
D.Consultation_Fee,  
PR.Procedure_Name,  
PR.Procedure_Charges,  
R.Severity_Score,  
R.Report_Status  
FROM tblPatient_Infomation AS P  
INNER JOIN tblMainMaster_Advent_health AS M  
ON P.Patient_ID = M.Patient_ID  
INNER JOIN tblDoctor_Information AS D  
ON M.Cardiologist = D.Doctor_Name  
INNER JOIN tblProcedure_Scans AS PR  
ON P.Patient_ID = PR.Patient_ID  
INNER JOIN tblReports_tests AS R  
ON P.Patient_ID = R.Patient_ID  
WHERE D.Consultation_Fee > 1500  
AND R.Report_Status = 'Finalized'  
AND R.Severity_Score > 60  
AND PR.Procedure_Status = 'Completed'  
AND PR.Procedure_Charges > 4000  
ORDER BY R.Severity_Score DESC;  
49. Find patients with all four types of information  
Find  
Find patients who have a clinical record, procedure, finalized report and billing record, where the procedure charge is above  
4,500, report severity is above 50, and pending billing is greater than 3,000.  
Query  
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
M.Cardiologist,  
M.Consultancy_Fee,  
PR.Procedure_Name,  
PR.Procedure_Charges,  
R.Report_Status,  
R.Severity_Score,  
B.Total_Amount,  
B.Paid_Amount,  
B.Pending_Amount  
FROM tblPatient_Infomation AS P  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
34  
INNER JOIN tblMainMaster_Advent_health AS M  
ON P.Patient_ID = M.Patient_ID  
INNER JOIN tblProcedure_Scans AS PR  
ON P.Patient_ID = PR.Patient_ID  
INNER JOIN tblReports_tests AS R  
ON P.Patient_ID = R.Patient_ID  
INNER JOIN tblBilling_Summary AS B  
ON P.Patient_ID = B.Patient_ID  
WHERE PR.Procedure_Charges > 4500  
AND R.Report_Status = 'Finalized'  
AND R.Severity_Score > 50  
AND B.Pending_Amount > 3000  
ORDER BY B.Pending_Amount DESC;  
50. Find patients whose procedure is above average  
Find  
Find patients who underwent a Completed procedure costing more than the average procedure charge, have a finalized report with  
severity above 50, and have a pending bill greater than 2,000.  
Query  
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
PR.Procedure_Name,  
PR.Procedure_Charges,  
R.Severity_Score,  
B.Pending_Amount  
FROM tblPatient_Infomation AS P  
INNER JOIN tblProcedure_Scans AS PR  
ON P.Patient_ID = PR.Patient_ID  
INNER JOIN tblReports_tests AS R  
ON P.Patient_ID = R.Patient_ID  
INNER JOIN tblBilling_Summary AS B  
ON P.Patient_ID = B.Patient_ID  
WHERE PR.Procedure_Status = 'Completed'  
AND PR.Procedure_Charges >  
(
SELECT AVG(Procedure_Charges)  
FROM tblProcedure_Scans  
)
AND R.Report_Status = 'Finalized'  
AND R.Severity_Score > 50  
AND B.Pending_Amount > 2000  
ORDER BY PR.Procedure_Charges DESC;  
51. Patient-level aggregation across 4 tables  
Find  
Find patients who have at least two procedures, total procedure charges greater than 10,000, at least one report with severity  
above 50, and total pending billing greater than 5,000.  
Query  
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
COUNT(DISTINCT PR.[Procedure_ID ]) AS Procedure_Count,  
SUM(PR.Procedure_Charges) AS Total_Procedure_Charges,  
MAX(R.Severity_Score) AS Maximum_Severity,  
SUM(B.Pending_Amount) AS Total_Pending  
FROM tblPatient_Infomation AS P  
INNER JOIN tblMainMaster_Advent_health AS M  
ON P.Patient_ID = M.Patient_ID  
INNER JOIN tblProcedure_Scans AS PR  
ON P.Patient_ID = PR.Patient_ID  
INNER JOIN tblReports_tests AS R  
ON P.Patient_ID = R.Patient_ID  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
35  
INNER JOIN tblBilling_Summary AS B  
ON P.Patient_ID = B.Patient_ID  
GROUP BY  
P.Patient_ID,  
P.First_Name,  
P.Last_Name  
HAVING COUNT(DISTINCT PR.[Procedure_ID ]) >= 2  
AND SUM(PR.Procedure_Charges) > 10000  
AND MAX(R.Severity_Score) > 50  
AND SUM(B.Pending_Amount) > 5000  
ORDER BY Total_Pending DESC;  
52. Find high-value patients by doctor  
Find  
Find doctors whose patients have at least three procedures in total, total procedure charges above 15,000, and total pending  
billing above 5,000. Only include doctors whose consultation fee is greater than 1,000.  
Query  
SELECT  
D.Doctor_Name,  
D.Experience_Years,  
D.Consultation_Fee,  
COUNT(DISTINCT PR.[Procedure_ID ]) AS Procedure_Count,  
SUM(PR.Procedure_Charges) AS Total_Procedure_Charges,  
SUM(B.Pending_Amount) AS Total_Pending  
FROM tblDoctor_Information AS D  
INNER JOIN tblMainMaster_Advent_health AS M  
ON D.Doctor_Name = M.Cardiologist  
INNER JOIN tblPatient_Infomation AS P  
ON M.Patient_ID = P.Patient_ID  
INNER JOIN tblProcedure_Scans AS PR  
ON P.Patient_ID = PR.Patient_ID  
INNER JOIN tblBilling_Summary AS B  
ON P.Patient_ID = B.Patient_ID  
WHERE D.Consultation_Fee > 1000  
GROUP BY  
D.Doctor_Name,  
D.Experience_Years,  
D.Consultation_Fee  
HAVING COUNT(DISTINCT PR.[Procedure_ID ]) >= 3  
AND SUM(PR.Procedure_Charges) > 15000  
AND SUM(B.Pending_Amount) > 5000  
ORDER BY Total_Procedure_Charges DESC;  
53. Find departments with serious cases and outstanding bills  
Find  
Find departments having more than 100 procedures, an average procedure charge above 4,000, at least one finalized report with  
severity above 70, and total pending billing above 100,000.  
Query  
SELECT  
PR.Department,  
COUNT(DISTINCT PR.[Procedure_ID ]) AS Procedure_Count,  
AVG(PR.Procedure_Charges) AS Average_Charge,  
MAX(R.Severity_Score) AS Maximum_Severity,  
SUM(B.Pending_Amount) AS Total_Pending  
FROM tblProcedure_Scans AS PR  
INNER JOIN tblPatient_Infomation AS P  
ON PR.Patient_ID = P.Patient_ID  
INNER JOIN tblReports_tests AS R  
ON P.Patient_ID = R.Patient_ID  
INNER JOIN tblBilling_Summary AS B  
ON P.Patient_ID = B.Patient_ID  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
36  
WHERE R.Report_Status = 'Finalized'  
GROUP BY PR.Department  
HAVING COUNT(DISTINCT PR.[Procedure_ID ]) > 100  
AND AVG(PR.Procedure_Charges) > 4000  
AND MAX(R.Severity_Score) > 70  
AND SUM(B.Pending_Amount) > 100000  
ORDER BY Total_Pending DESC;  
54. Find doctors whose patients have severe reports  
Find  
Find doctors who have treated more than 20 patients, whose average consultancy fee is above 1,000, and whose patients have at  
least one finalized report with severity above 70.  
Query  
SELECT  
D.Doctor_Name,  
D.Experience_Years,  
D.Consultation_Fee,  
COUNT(DISTINCT P.Patient_ID) AS Patient_Count,  
AVG(M.Consultancy_Fee) AS Average_Consultancy_Fee,  
MAX(R.Severity_Score) AS Maximum_Severity  
FROM tblDoctor_Information AS D  
INNER JOIN tblMainMaster_Advent_health AS M  
ON D.Doctor_Name = M.Cardiologist  
INNER JOIN tblPatient_Infomation AS P  
ON M.Patient_ID = P.Patient_ID  
INNER JOIN tblReports_tests AS R  
ON P.Patient_ID = R.Patient_ID  
WHERE R.Report_Status = 'Finalized'  
GROUP BY  
D.Doctor_Name,  
D.Experience_Years,  
D.Consultation_Fee  
HAVING COUNT(DISTINCT P.Patient_ID) > 20  
AND AVG(M.Consultancy_Fee) > 1000  
AND MAX(R.Severity_Score) > 70  
ORDER BY Maximum_Severity DESC;  
55. Find patients with procedure + report but NO billing  
Find  
Find patients who have a Completed procedure and a Finalized report with severity above 50, but do not have any billing record.  
Query  
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
PR.Procedure_Name,  
PR.Procedure_Charges,  
R.Report_Status,  
R.Severity_Score  
FROM tblPatient_Infomation AS P  
INNER JOIN tblMainMaster_Advent_health AS M  
ON P.Patient_ID = M.Patient_ID  
INNER JOIN tblProcedure_Scans AS PR  
ON P.Patient_ID = PR.Patient_ID  
INNER JOIN tblReports_tests AS R  
ON P.Patient_ID = R.Patient_ID  
WHERE PR.Procedure_Status = 'Completed'  
AND R.Report_Status = 'Finalized'  
AND R.Severity_Score > 50  
AND NOT EXISTS  
(
SELECT 1  
FROM tblBilling_Summary AS B  
WHERE B.Patient_ID = P.Patient_ID  
)
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
ORDER BY R.Severity_Score DESC;  
37  
56. Find patients with billing but no procedure  
Find  
Find patients who have a billing record with a total amount greater than 20,000 but do not have a corresponding procedure record.  
The patient must have a valid email address.  
Query  
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
P.Email,  
B.Total_Amount,  
B.Paid_Amount,  
B.Pending_Amount,  
B.Billing_Status  
FROM tblPatient_Infomation AS P  
INNER JOIN tblMainMaster_Advent_health AS M  
ON P.Patient_ID = M.Patient_ID  
INNER JOIN tblBilling_Summary AS B  
ON P.Patient_ID = B.Patient_ID  
WHERE P.Email IS NOT NULL  
AND B.Total_Amount > 20000  
AND NOT EXISTS  
(
SELECT 1  
FROM tblProcedure_Scans AS PR  
WHERE PR.Patient_ID = P.Patient_ID  
)
ORDER BY B.Total_Amount DESC;  
57. Find patients with multiple completed procedures  
Find  
Find patients who have at least two Completed procedures, total procedure charges greater than 12,000, at least one finalized  
report, and total pending billing greater than 3,000.  
Query  
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
COUNT(DISTINCT PR.[Procedure_ID ]) AS Completed_Procedures,  
SUM(PR.Procedure_Charges) AS Total_Charges,  
MAX(R.Severity_Score) AS Max_Severity,  
SUM(B.Pending_Amount) AS Total_Pending  
FROM tblPatient_Infomation AS P  
INNER JOIN tblMainMaster_Advent_health AS M  
ON P.Patient_ID = M.Patient_ID  
INNER JOIN tblProcedure_Scans AS PR  
ON P.Patient_ID = PR.Patient_ID  
INNER JOIN tblReports_tests AS R  
ON P.Patient_ID = R.Patient_ID  
INNER JOIN tblBilling_Summary AS B  
ON P.Patient_ID = B.Patient_ID  
WHERE PR.Procedure_Status = 'Completed'  
AND R.Report_Status = 'Finalized'  
GROUP BY  
P.Patient_ID,  
P.First_Name,  
P.Last_Name  
HAVING COUNT(DISTINCT PR.[Procedure_ID ]) >= 2  
AND SUM(PR.Procedure_Charges) > 12000  
AND SUM(B.Pending_Amount) > 3000  
ORDER BY Total_Charges DESC;  
58. Find doctors with patients from multiple cities  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
38  
Find  
Find doctors who have treated patients from at least three different cities, have more than 50 patients, have an average  
consultancy fee above 1,000, and have at least one procedure costing more than 5,000.  
Query  
SELECT  
D.Doctor_Name,  
D.Experience_Years,  
D.Consultation_Fee,  
COUNT(DISTINCT P.Patient_ID) AS Patient_Count,  
COUNT(DISTINCT P.City) AS City_Count,  
AVG(M.Consultancy_Fee) AS Average_Consultancy,  
MAX(PR.Procedure_Charges) AS Maximum_Procedure_Charge  
FROM tblDoctor_Information AS D  
INNER JOIN tblMainMaster_Advent_health AS M  
ON D.Doctor_Name = M.Cardiologist  
INNER JOIN tblPatient_Infomation AS P  
ON M.Patient_ID = P.Patient_ID  
INNER JOIN tblProcedure_Scans AS PR  
ON P.Patient_ID = PR.Patient_ID  
GROUP BY  
D.Doctor_Name,  
D.Experience_Years,  
D.Consultation_Fee  
HAVING COUNT(DISTINCT P.Patient_ID) > 50  
AND COUNT(DISTINCT P.City) >= 3  
AND AVG(M.Consultancy_Fee) > 1000  
AND MAX(PR.Procedure_Charges) > 5000  
ORDER BY Patient_Count DESC;  
59. Find high-value patients with a specific payment method  
Find  
Find patients from Seattle, Chicago or Las Vegas who have a High or Critical priority procedure, a finalized report with severity  
above 60, and bills paid through Debit Card or Credit Card where the total billing amount exceeds 30,000.  
Query  
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
P.City,  
PR.Procedure_Name,  
PR.Priority,  
PR.Procedure_Charges,  
R.Report_Status,  
R.Severity_Score,  
B.Payment_Method,  
B.Total_Amount,  
B.Paid_Amount,  
B.Pending_Amount  
FROM tblPatient_Infomation AS P  
INNER JOIN tblMainMaster_Advent_health AS M  
ON P.Patient_ID = M.Patient_ID  
INNER JOIN tblProcedure_Scans AS PR  
ON P.Patient_ID = PR.Patient_ID  
INNER JOIN tblReports_tests AS R  
ON P.Patient_ID = R.Patient_ID  
INNER JOIN tblBilling_Summary AS B  
ON P.Patient_ID = B.Patient_ID  
WHERE P.City IN ('Seattle', 'Chicago', 'Las Vegas')  
AND PR.Priority IN ('High', 'Critical')  
AND R.Report_Status = 'Finalized'  
AND R.Severity_Score > 60  
AND B.Payment_Method IN ('Debit Card', 'Credit Card')  
AND B.Total_Amount > 30000  
ORDER BY  
B.Total_Amount DESC,  
R.Severity_Score DESC;  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
39  
60.   MASTER QUESTION — Everything mixed  
This is the level I recommend you practice heavily.  
Find  
Find the top 20 patients who are female, live in Seattle, Las Vegas or Chicago, have an available email address, have Diabetes or  
Hypertension in their medical history, and are treated by a doctor with at least 10 years of experience. The patient must have  
at least one Completed procedure with High or Critical priority and a procedure charge greater than the overall average  
procedure charge. The patient must also have a Finalized report with severity greater than 60 and a billing record with total  
amount greater than 30,000 and pending amount greater than 5,000. Exclude patients whose risk factor contains Smoking. Display  
the patient details, doctor details, procedure details, report severity and billing information.  
Query  
SELECT TOP 20  
-- Patient  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
P.City,  
P.Gender,  
P.Blood_Group,  
P.Email,  
-- Clinical  
M.Medical_History,  
M.Risk_Factors,  
M.Consultancy_Fee,  
-- Doctor  
D.Doctor_Name,  
D.Experience_Years,  
D.Consultation_Fee,  
-- Procedure  
PR.Procedure_Name,  
PR.Department,  
PR.Procedure_Status,  
PR.Priority,  
PR.Procedure_Charges,  
-- Report  
R.Report_Status,  
R.Severity_Score,  
R.Pathology_Detected,  
-- Billing  
B.Payment_Method,  
B.Total_Amount,  
B.Paid_Amount,  
B.Pending_Amount,  
B.Billing_Status  
FROM tblPatient_Infomation AS P  
INNER JOIN tblMainMaster_Advent_health AS M  
ON P.Patient_ID = M.Patient_ID  
INNER JOIN tblDoctor_Information AS D  
ON M.Cardiologist = D.Doctor_Name  
INNER JOIN tblProcedure_Scans AS PR  
ON P.Patient_ID = PR.Patient_ID  
INNER JOIN tblReports_tests AS R  
ON P.Patient_ID = R.Patient_ID  
INNER JOIN tblBilling_Summary AS B  
ON P.Patient_ID = B.Patient_ID  
WHERE  
-- Patient conditions  
P.Gender = 'FEMALE'  
AND P.City IN  
(
'Seattle',  
'Las Vegas',  
'Chicago'  
)
AND P.Email IS NOT NULL  
-- Clinical conditions  
AND M.Medical_History IN  
(
'Diabetes',  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
40  
'Hypertension'  
)
AND M.Risk_Factors NOT LIKE '%Smoking%'  
-- Doctor condition  
AND D.Experience_Years >= 10  
-- Procedure conditions  
AND PR.Procedure_Status = 'Completed'  
AND PR.Priority IN  
(
'High',  
'Critical'  
)
AND PR.Procedure_Charges >  
(
SELECT AVG(Procedure_Charges)  
FROM tblProcedure_Scans  
)
-- Report conditions  
AND R.Report_Status = 'Finalized'  
AND R.Severity_Score > 60  
-- Billing conditions  
AND B.Total_Amount > 30000  
AND B.Pending_Amount > 5000  
ORDER BY  
B.Pending_Amount DESC,  
R.Severity_Score DESC,  
PR.Procedure_Charges DESC;  
----------------------------------------------------------------------------------------------------------------------------------  
------------------------------------------------------------  
LEVEL 4 — CTE + MULTIPLE JOINS  
61. Find high-value patients using a CTE  
Find  
Find the top 20 patients who have completed procedures with total procedure charges greater than 10,000,  
total pending billing greater than 5,000, and at least one finalized report with severity greater than 60.  
Exclude patients from Chicago. Display patient, procedure, report and billing information.  
Query  
WITH PatientProcedureSummary AS  
(
SELECT  
Patient_ID,  
COUNT(DISTINCT [Procedure_ID ]) AS Procedure_Count,  
SUM(Procedure_Charges) AS Total_Procedure_Charges,  
MAX(Procedure_Charges) AS Maximum_Procedure_Charge  
FROM tblProcedure_Scans  
WHERE Procedure_Status = 'Completed'  
GROUP BY Patient_ID  
),  
PatientBillingSummary AS  
(
SELECT  
Patient_ID,  
SUM(Total_Amount) AS Total_Billed,  
SUM(Paid_Amount) AS Total_Paid,  
SUM(Pending_Amount) AS Total_Pending  
FROM tblBilling_Summary  
GROUP BY Patient_ID  
),  
PatientReportSummary AS  
(
SELECT  
Patient_ID,  
MAX(Severity_Score) AS Maximum_Severity  
FROM tblReports_tests  
WHERE Report_Status = 'Finalized'  
GROUP BY Patient_ID  
)
SELECT TOP 20  
P.Patient_ID,  
P.First_Name,  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
41  
P.Last_Name,  
P.City,  
M.Cardiologist,  
M.Consultancy_Fee,  
PPS.Procedure_Count,  
PPS.Total_Procedure_Charges,  
PRS.Maximum_Severity,  
PBS.Total_Billed,  
PBS.Total_Paid,  
PBS.Total_Pending  
FROM tblPatient_Infomation AS P  
INNER JOIN tblMainMaster_Advent_health AS M  
ON P.Patient_ID = M.Patient_ID  
INNER JOIN PatientProcedureSummary AS PPS  
ON P.Patient_ID = PPS.Patient_ID  
INNER JOIN PatientBillingSummary AS PBS  
ON P.Patient_ID = PBS.Patient_ID  
INNER JOIN PatientReportSummary AS PRS  
ON P.Patient_ID = PRS.Patient_ID  
WHERE P.City <> 'Chicago'  
AND PPS.Total_Procedure_Charges > 10000  
AND PBS.Total_Pending > 5000  
AND PRS.Maximum_Severity > 60  
ORDER BY  
PBS.Total_Pending DESC,  
PPS.Total_Procedure_Charges DESC;  
62. Find doctors with high-value patients  
Find  
Find doctors whose patients have completed at least three procedures in total, total procedure charges exceed 15,000, and total  
pending billing exceeds 5,000. Only include doctors whose consultation fee is greater than 1,000.  
Query  
WITH DoctorPatientSummary AS  
(
SELECT  
M.Cardiologist,  
COUNT(DISTINCT M.Patient_ID) AS Patient_Count,  
SUM(PR.Procedure_Charges) AS Total_Procedure_Charges,  
COUNT(DISTINCT PR.[Procedure_ID ]) AS Procedure_Count  
FROM tblMainMaster_Advent_health AS M  
INNER JOIN tblProcedure_Scans AS PR  
ON M.Patient_ID = PR.Patient_ID  
GROUP BY M.Cardiologist  
),  
DoctorBillingSummary AS  
(
SELECT  
M.Cardiologist,  
SUM(B.Pending_Amount) AS Total_Pending  
FROM tblMainMaster_Advent_health AS M  
INNER JOIN tblBilling_Summary AS B  
ON M.Patient_ID = B.Patient_ID  
GROUP BY M.Cardiologist  
)
SELECT  
D.Doctor_Name,  
D.Experience_Years,  
D.Consultation_Fee,  
DPS.Patient_Count,  
DPS.Procedure_Count,  
DPS.Total_Procedure_Charges,  
DBS.Total_Pending  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
FROM tblDoctor_Information AS D  
42  
INNER JOIN DoctorPatientSummary AS DPS  
ON D.Doctor_Name = DPS.Cardiologist  
INNER JOIN DoctorBillingSummary AS DBS  
ON D.Doctor_Name = DBS.Cardiologist  
WHERE D.Consultation_Fee > 1000  
AND DPS.Procedure_Count >= 3  
AND DPS.Total_Procedure_Charges > 15000  
AND DBS.Total_Pending > 5000  
ORDER BY  
DPS.Total_Procedure_Charges DESC;  
63. Find departments with serious and expensive cases  
Find  
Find departments having more than 100 completed procedures, an average procedure charge above 4,000, a maximum report severity  
above 70, and total pending billing above 100,000.  
Query  
WITH DepartmentProcedure AS  
(
SELECT  
Department,  
COUNT(DISTINCT [Procedure_ID ]) AS Procedure_Count,  
AVG(Procedure_Charges) AS Average_Charge,  
SUM(Procedure_Charges) AS Total_Charges  
FROM tblProcedure_Scans  
WHERE Procedure_Status = 'Completed'  
GROUP BY Department  
),  
DepartmentReport AS  
(
SELECT  
PR.Department,  
MAX(R.Severity_Score) AS Maximum_Severity  
FROM tblProcedure_Scans AS PR  
INNER JOIN tblReports_tests AS R  
ON PR.Patient_ID = R.Patient_ID  
WHERE R.Report_Status = 'Finalized'  
GROUP BY PR.Department  
),  
DepartmentBilling AS  
(
SELECT  
PR.Department,  
SUM(B.Pending_Amount) AS Total_Pending  
FROM tblProcedure_Scans AS PR  
INNER JOIN tblBilling_Summary AS B  
ON PR.Patient_ID = B.Patient_ID  
GROUP BY PR.Department  
)
SELECT  
D.Department,  
DP.Procedure_Count,  
DP.Average_Charge,  
DP.Total_Charges,  
DR.Maximum_Severity,  
DB.Total_Pending  
FROM  
DepartmentProcedure AS DP  
INNER JOIN DepartmentReport AS DR  
ON DP.Department = DR.Department  
INNER JOIN DepartmentBilling AS DB  
ON DP.Department = DB.Department  
INNER JOIN  
(
SELECT DISTINCT Department  
FROM tblProcedure_Scans  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
43  
) AS D  
ON DP.Department = D.Department  
WHERE DP.Procedure_Count > 100  
AND DP.Average_Charge > 4000  
AND DR.Maximum_Severity > 70  
AND DB.Total_Pending > 100000  
ORDER BY DB.Total_Pending DESC;  
64. Find patients whose spending is above average  
Find  
Find patients whose total completed-procedure charges are greater than the average total procedure charges per patient, who have a  
finalized report with severity above 50 and total pending billing above 3,000.  
Query  
WITH PatientProcedure AS  
(
SELECT  
Patient_ID,  
COUNT(DISTINCT [Procedure_ID ]) AS Procedure_Count,  
SUM(Procedure_Charges) AS Total_Procedure_Charges  
FROM tblProcedure_Scans  
WHERE Procedure_Status = 'Completed'  
GROUP BY Patient_ID  
),  
PatientReport AS  
(
SELECT  
Patient_ID,  
MAX(Severity_Score) AS Maximum_Severity  
FROM tblReports_tests  
WHERE Report_Status = 'Finalized'  
GROUP BY Patient_ID  
),  
PatientBilling AS  
(
SELECT  
Patient_ID,  
SUM(Pending_Amount) AS Total_Pending  
FROM tblBilling_Summary  
GROUP BY Patient_ID  
),  
AveragePatientSpend AS  
(
SELECT  
AVG(Total_Procedure_Charges) AS Average_Spend  
FROM PatientProcedure  
)
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
PP.Procedure_Count,  
PP.Total_Procedure_Charges,  
PR.Maximum_Severity,  
PB.Total_Pending  
FROM tblPatient_Infomation AS P  
INNER JOIN PatientProcedure AS PP  
ON P.Patient_ID = PP.Patient_ID  
INNER JOIN PatientReport AS PR  
ON P.Patient_ID = PR.Patient_ID  
INNER JOIN PatientBilling AS PB  
ON P.Patient_ID = PB.Patient_ID  
CROSS JOIN AveragePatientSpend AS APS  
WHERE PP.Total_Procedure_Charges > APS.Average_Spend  
AND PR.Maximum_Severity > 50  
AND PB.Total_Pending > 3000  
ORDER BY PP.Total_Procedure_Charges DESC;  
65. Find patients treated by experienced doctors  
Find  
Find patients treated by doctors with at least 10 years of experience who have a completed High or Critical priority procedure  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
44  
costing more than 5,000 and a finalized report with severity above 60.  
Query  
WITH ExperiencedDoctors AS  
(
SELECT  
Doctor_ID,  
Doctor_Name,  
Experience_Years,  
Consultation_Fee  
FROM tblDoctor_Information  
WHERE Experience_Years >= 10  
),  
SeriousReports AS  
(
SELECT  
Patient_ID,  
MAX(Severity_Score) AS Maximum_Severity  
FROM tblReports_tests  
WHERE Report_Status = 'Finalized'  
GROUP BY Patient_ID  
)
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
D.Doctor_Name,  
D.Experience_Years,  
D.Consultation_Fee,  
PR.Procedure_Name,  
PR.Priority,  
PR.Procedure_Charges,  
SR.Maximum_Severity,  
B.Total_Amount,  
B.Pending_Amount  
FROM tblPatient_Infomation AS P  
INNER JOIN tblMainMaster_Advent_health AS M  
ON P.Patient_ID = M.Patient_ID  
INNER JOIN ExperiencedDoctors AS D  
ON M.Cardiologist = D.Doctor_Name  
INNER JOIN tblProcedure_Scans AS PR  
ON P.Patient_ID = PR.Patient_ID  
INNER JOIN SeriousReports AS SR  
ON P.Patient_ID = SR.Patient_ID  
INNER JOIN tblBilling_Summary AS B  
ON P.Patient_ID = B.Patient_ID  
WHERE PR.Procedure_Status = 'Completed'  
AND PR.Priority IN ('High', 'Critical')  
AND PR.Procedure_Charges > 5000  
AND SR.Maximum_Severity > 60  
ORDER BY SR.Maximum_Severity DESC;  
66. Find patients with repeated procedures  
Find  
Find patients who have undergone at least three procedures, where at least two procedures were Completed, total procedure charges  
exceed 15,000, and the patient has at least one finalized report.  
Query  
WITH PatientProcedureSummary AS  
(
SELECT  
Patient_ID,  
COUNT(DISTINCT [Procedure_ID ]) AS Total_Procedures,  
SUM(  
CASE  
WHEN Procedure_Status = 'Completed'  
THEN 1  
ELSE 0  
END  
) AS Completed_Procedures,  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
45  
SUM(Procedure_Charges) AS Total_Charges  
FROM tblProcedure_Scans  
GROUP BY Patient_ID  
),  
PatientReports AS  
(
SELECT  
Patient_ID,  
COUNT(*) AS Finalized_Report_Count  
FROM tblReports_tests  
WHERE Report_Status = 'Finalized'  
GROUP BY Patient_ID  
)
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
PPS.Total_Procedures,  
PPS.Completed_Procedures,  
PPS.Total_Charges,  
PR.Finalized_Report_Count,  
M.Cardiologist,  
M.Consultancy_Fee  
FROM tblPatient_Infomation AS P  
INNER JOIN tblMainMaster_Advent_health AS M  
ON P.Patient_ID = M.Patient_ID  
INNER JOIN PatientProcedureSummary AS PPS  
ON P.Patient_ID = PPS.Patient_ID  
INNER JOIN PatientReports AS PR  
ON P.Patient_ID = PR.Patient_ID  
WHERE PPS.Total_Procedures >= 3  
AND PPS.Completed_Procedures >= 2  
AND PPS.Total_Charges > 15000  
ORDER BY PPS.Total_Charges DESC;  
Finding  
This identifies repeat-treatment patients rather than simply looking at one procedure.  
________________________________________  
67. Find patients with no billing despite treatment  
Find  
Find patients who have at least one completed procedure and at least one finalized report, but do not have any billing record.  
Include only patients whose procedure charges exceed 5,000.  
Query  
WITH CompletedProcedures AS  
(
SELECT  
Patient_ID,  
SUM(Procedure_Charges) AS Total_Procedure_Charges,  
COUNT(DISTINCT [Procedure_ID ]) AS Procedure_Count  
FROM tblProcedure_Scans  
WHERE Procedure_Status = 'Completed'  
GROUP BY Patient_ID  
),  
FinalizedReports AS  
(
SELECT  
Patient_ID,  
MAX(Severity_Score) AS Maximum_Severity  
FROM tblReports_tests  
WHERE Report_Status = 'Finalized'  
GROUP BY Patient_ID  
)
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
M.Cardiologist,  
CP.Procedure_Count,  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
CP.Total_Procedure_Charges,  
46  
FR.Maximum_Severity  
FROM tblPatient_Infomation AS P  
INNER JOIN tblMainMaster_Advent_health AS M  
ON P.Patient_ID = M.Patient_ID  
INNER JOIN CompletedProcedures AS CP  
ON P.Patient_ID = CP.Patient_ID  
INNER JOIN FinalizedReports AS FR  
ON P.Patient_ID = FR.Patient_ID  
WHERE CP.Total_Procedure_Charges > 5000  
AND NOT EXISTS  
(
SELECT 1  
FROM tblBilling_Summary AS B  
WHERE B.Patient_ID = P.Patient_ID  
)
ORDER BY CP.Total_Procedure_Charges DESC;  
68. Find patients with billing but no procedure  
Find  
Find patients whose total billing exceeds 30,000 and whose pending amount exceeds 5,000, but who have no procedure record. Include  
only patients with a valid email address.  
Query  
WITH PatientBilling AS  
(
SELECT  
Patient_ID,  
SUM(Total_Amount) AS Total_Billed,  
SUM(Paid_Amount) AS Total_Paid,  
SUM(Pending_Amount) AS Total_Pending  
FROM tblBilling_Summary  
GROUP BY Patient_ID  
),  
PatientClinical AS  
(
SELECT  
Patient_ID,  
MAX(Cardiologist) AS Cardiologist,  
AVG(Consultancy_Fee) AS Average_Consultancy_Fee  
FROM tblMainMaster_Advent_health  
GROUP BY Patient_ID  
)
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
P.Email,  
PC.Cardiologist,  
PC.Average_Consultancy_Fee,  
PB.Total_Billed,  
PB.Total_Paid,  
PB.Total_Pending  
FROM tblPatient_Infomation AS P  
INNER JOIN PatientBilling AS PB  
ON P.Patient_ID = PB.Patient_ID  
INNER JOIN PatientClinical AS PC  
ON P.Patient_ID = PC.Patient_ID  
INNER JOIN  
(
SELECT DISTINCT Patient_ID  
FROM tblBilling_Summary  
) AS B  
ON P.Patient_ID = B.Patient_ID  
WHERE P.Email IS NOT NULL  
AND PB.Total_Billed > 30000  
AND PB.Total_Pending > 5000  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
47  
AND NOT EXISTS  
(
SELECT 1  
FROM tblProcedure_Scans AS PR  
WHERE PR.Patient_ID = P.Patient_ID  
)
ORDER BY PB.Total_Pending DESC;  
71. Find high-risk patients with no cancelled billing  
Find  
Find patients who have Diabetes or Hypertension, a High or Critical priority completed procedure, a finalized report with severity  
above 60, and pending billing above 3,000. Exclude any patient who has even one Cancelled billing record.  
Query  
WITH HighRiskPatients AS  
(
SELECT DISTINCT  
Patient_ID  
FROM tblMainMaster_Advent_health  
WHERE Medical_History IN ('Diabetes', 'Hypertension')  
),  
SeriousProcedures AS  
(
SELECT  
Patient_ID,  
MAX(Procedure_Charges) AS Maximum_Procedure_Charge,  
COUNT(DISTINCT [Procedure_ID ]) AS Procedure_Count  
FROM tblProcedure_Scans  
WHERE Procedure_Status = 'Completed'  
AND Priority IN ('High', 'Critical')  
GROUP BY Patient_ID  
),  
SeriousReports AS  
(
SELECT  
Patient_ID,  
MAX(Severity_Score) AS Maximum_Severity  
FROM tblReports_tests  
WHERE Report_Status = 'Finalized'  
GROUP BY Patient_ID  
),  
BillingSummary AS  
(
SELECT  
Patient_ID,  
SUM(Pending_Amount) AS Total_Pending  
FROM tblBilling_Summary  
GROUP BY Patient_ID  
)
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
P.City,  
M.Cardiologist,  
M.Medical_History,  
SP.Procedure_Count,  
SP.Maximum_Procedure_Charge,  
SR.Maximum_Severity,  
BS.Total_Pending  
FROM tblPatient_Infomation AS P  
INNER JOIN HighRiskPatients AS HR  
ON P.Patient_ID = HR.Patient_ID  
INNER JOIN tblMainMaster_Advent_health AS M  
ON P.Patient_ID = M.Patient_ID  
INNER JOIN SeriousProcedures AS SP  
ON P.Patient_ID = SP.Patient_ID  
INNER JOIN SeriousReports AS SR  
ON P.Patient_ID = SR.Patient_ID  
INNER JOIN BillingSummary AS BS  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
ON P.Patient_ID = BS.Patient_ID  
48  
WHERE SR.Maximum_Severity > 60  
AND BS.Total_Pending > 3000  
AND NOT EXISTS  
(
SELECT 1  
FROM tblBilling_Summary AS B  
WHERE B.Patient_ID = P.Patient_ID  
AND B.Billing_Status = 'Cancelled'  
)
ORDER BY BS.Total_Pending DESC;  
72. Find patients meeting multiple average conditions  
Find  
Find patients whose total completed-procedure charges are above the average patient total, whose total pending billing is above  
the average patient pending amount, and whose maximum report severity is above 60.  
Query  
WITH PatientProcedure AS  
(
SELECT  
Patient_ID,  
SUM(Procedure_Charges) AS Total_Procedure_Charges  
FROM tblProcedure_Scans  
WHERE Procedure_Status = 'Completed'  
GROUP BY Patient_ID  
),  
PatientBilling AS  
(
SELECT  
Patient_ID,  
SUM(Pending_Amount) AS Total_Pending  
FROM tblBilling_Summary  
GROUP BY Patient_ID  
),  
PatientReport AS  
(
SELECT  
Patient_ID,  
MAX(Severity_Score) AS Maximum_Severity  
FROM tblReports_tests  
GROUP BY Patient_ID  
),  
AverageValues AS  
(
SELECT  
AVG(Total_Procedure_Charges) AS Average_Procedure_Spend  
FROM PatientProcedure  
),  
AveragePending AS  
(
SELECT  
AVG(Total_Pending) AS Average_Pending  
FROM PatientBilling  
)
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
PP.Total_Procedure_Charges,  
PB.Total_Pending,  
PR.Maximum_Severity,  
M.Cardiologist  
FROM tblPatient_Infomation AS P  
INNER JOIN tblMainMaster_Advent_health AS M  
ON P.Patient_ID = M.Patient_ID  
INNER JOIN PatientProcedure AS PP  
ON P.Patient_ID = PP.Patient_ID  
INNER JOIN PatientBilling AS PB  
ON P.Patient_ID = PB.Patient_ID  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
49  
INNER JOIN PatientReport AS PR  
ON P.Patient_ID = PR.Patient_ID  
CROSS JOIN AverageValues AS AV  
CROSS JOIN AveragePending AS AP  
WHERE PP.Total_Procedure_Charges > AV.Average_Procedure_Spend  
AND PB.Total_Pending > AP.Average_Pending  
AND PR.Maximum_Severity > 60  
ORDER BY PB.Total_Pending DESC;  
73. Find doctors with no low-value patients  
Find  
Find doctors who have more than 30 patients and an average consultancy fee above 1,000, but do not have any patient whose  
consultancy fee is below 500.  
Query  
WITH DoctorPatientSummary AS  
(
SELECT  
Cardiologist,  
COUNT(DISTINCT Patient_ID) AS Patient_Count,  
AVG(Consultancy_Fee) AS Average_Fee,  
MIN(Consultancy_Fee) AS Minimum_Fee  
FROM tblMainMaster_Advent_health  
GROUP BY Cardiologist  
)
SELECT  
D.Doctor_Name,  
D.Experience_Years,  
D.Consultation_Fee,  
DPS.Patient_Count,  
DPS.Average_Fee,  
DPS.Minimum_Fee,  
COUNT(DISTINCT PR.[Procedure_ID ]) AS Procedure_Count  
FROM tblDoctor_Information AS D  
INNER JOIN DoctorPatientSummary AS DPS  
ON D.Doctor_Name = DPS.Cardiologist  
INNER JOIN tblMainMaster_Advent_health AS M  
ON D.Doctor_Name = M.Cardiologist  
INNER JOIN tblProcedure_Scans AS PR  
ON M.Patient_ID = PR.Patient_ID  
WHERE DPS.Patient_Count > 30  
AND DPS.Average_Fee > 1000  
AND NOT EXISTS  
(
SELECT 1  
FROM tblMainMaster_Advent_health AS M2  
WHERE M2.Cardiologist = D.Doctor_Name  
AND M2.Consultancy_Fee < 500  
)
GROUP BY  
D.Doctor_Name,  
D.Experience_Years,  
D.Consultation_Fee,  
DPS.Patient_Count,  
DPS.Average_Fee,  
DPS.Minimum_Fee  
ORDER BY DPS.Average_Fee DESC;  
74. Find patients with both severe reports and multiple procedures  
Find  
Find patients who have at least two completed procedures, total procedure charges above 10,000, maximum report severity above 70,  
and at least one billing record with pending amount above 5,000.  
Query  
WITH ProcedureSummary AS  
(
SELECT  
Patient_ID,  
COUNT(DISTINCT [Procedure_ID ]) AS Procedure_Count,  
SUM(Procedure_Charges) AS Total_Charges  
FROM tblProcedure_Scans  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
50  
WHERE Procedure_Status = 'Completed'  
GROUP BY Patient_ID  
HAVING COUNT(DISTINCT [Procedure_ID ]) >= 2  
AND SUM(Procedure_Charges) > 10000  
),  
ReportSummary AS  
(
SELECT  
Patient_ID,  
MAX(Severity_Score) AS Maximum_Severity  
FROM tblReports_tests  
WHERE Report_Status = 'Finalized'  
GROUP BY Patient_ID  
HAVING MAX(Severity_Score) > 70  
),  
BillingSummary AS  
(
SELECT  
Patient_ID,  
MAX(Pending_Amount) AS Maximum_Pending  
FROM tblBilling_Summary  
GROUP BY Patient_ID  
HAVING MAX(Pending_Amount) > 5000  
)
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
P.City,  
M.Cardiologist,  
PS.Procedure_Count,  
PS.Total_Charges,  
RS.Maximum_Severity,  
BS.Maximum_Pending  
FROM tblPatient_Infomation AS P  
INNER JOIN tblMainMaster_Advent_health AS M  
ON P.Patient_ID = M.Patient_ID  
INNER JOIN ProcedureSummary AS PS  
ON P.Patient_ID = PS.Patient_ID  
INNER JOIN ReportSummary AS RS  
ON P.Patient_ID = RS.Patient_ID  
INNER JOIN BillingSummary AS BS  
ON P.Patient_ID = BS.Patient_ID  
ORDER BY  
PS.Total_Charges DESC,  
RS.Maximum_Severity DESC;  
75. Find patients with high treatment but low payment  
Find  
Find patients whose completed procedure charges exceed 15,000 but who have paid less than 50% of their total billing amount. The  
patient must also have a finalized report with severity above 50.  
Query  
WITH ProcedureSummary AS  
(
SELECT  
Patient_ID,  
SUM(Procedure_Charges) AS Total_Procedure_Charges  
FROM tblProcedure_Scans  
WHERE Procedure_Status = 'Completed'  
GROUP BY Patient_ID  
),  
BillingSummary AS  
(
SELECT  
Patient_ID,  
SUM(Total_Amount) AS Total_Billed,  
SUM(Paid_Amount) AS Total_Paid,  
SUM(Pending_Amount) AS Total_Pending  
FROM tblBilling_Summary  
GROUP BY Patient_ID  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
),  
51  
ReportSummary AS  
(
SELECT  
Patient_ID,  
MAX(Severity_Score) AS Maximum_Severity  
FROM tblReports_tests  
WHERE Report_Status = 'Finalized'  
GROUP BY Patient_ID  
)
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
PS.Total_Procedure_Charges,  
BS.Total_Billed,  
BS.Total_Paid,  
BS.Total_Pending,  
RS.Maximum_Severity  
FROM tblPatient_Infomation AS P  
INNER JOIN tblMainMaster_Advent_health AS M  
ON P.Patient_ID = M.Patient_ID  
INNER JOIN ProcedureSummary AS PS  
ON P.Patient_ID = PS.Patient_ID  
INNER JOIN BillingSummary AS BS  
ON P.Patient_ID = BS.Patient_ID  
INNER JOIN ReportSummary AS RS  
ON P.Patient_ID = RS.Patient_ID  
WHERE PS.Total_Procedure_Charges > 15000  
AND BS.Total_Paid < (BS.Total_Billed * 0.50)  
AND RS.Maximum_Severity > 50  
ORDER BY BS.Total_Pending DESC;  
76. Find doctors with serious patient populations  
Find  
Find doctors who have treated at least 50 distinct patients, at least 10 of their patients have finalized reports with severity  
above 60, and their average consultancy fee is greater than 1,000.  
Query  
WITH DoctorPatients AS  
(
SELECT  
Cardiologist,  
COUNT(DISTINCT Patient_ID) AS Total_Patients,  
AVG(Consultancy_Fee) AS Average_Consultancy_Fee  
FROM tblMainMaster_Advent_health  
GROUP BY Cardiologist  
),  
DoctorSeriousPatients AS  
(
SELECT  
M.Cardiologist,  
COUNT(DISTINCT R.Patient_ID) AS Serious_Patient_Count  
FROM tblMainMaster_Advent_health AS M  
INNER JOIN tblReports_tests AS R  
ON M.Patient_ID = R.Patient_ID  
WHERE R.Report_Status = 'Finalized'  
AND R.Severity_Score > 60  
GROUP BY M.Cardiologist  
)
SELECT  
D.Doctor_Name,  
D.Experience_Years,  
D.Consultation_Fee,  
DP.Total_Patients,  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
DP.Average_Consultancy_Fee,  
52  
DSP.Serious_Patient_Count,  
COUNT(DISTINCT PR.[Procedure_ID ]) AS Procedure_Count  
FROM tblDoctor_Information AS D  
INNER JOIN DoctorPatients AS DP  
ON D.Doctor_Name = DP.Cardiologist  
INNER JOIN DoctorSeriousPatients AS DSP  
ON D.Doctor_Name = DSP.Cardiologist  
INNER JOIN tblMainMaster_Advent_health AS M  
ON D.Doctor_Name = M.Cardiologist  
INNER JOIN tblProcedure_Scans AS PR  
ON M.Patient_ID = PR.Patient_ID  
WHERE DP.Total_Patients >= 50  
AND DP.Average_Consultancy_Fee > 1000  
AND DSP.Serious_Patient_Count >= 10  
GROUP BY  
D.Doctor_Name,  
D.Experience_Years,  
D.Consultation_Fee,  
DP.Total_Patients,  
DP.Average_Consultancy_Fee,  
DSP.Serious_Patient_Count  
ORDER BY DSP.Serious_Patient_Count DESC;  
________________________________________  
77. Find high-risk patients excluding specific cities  
Find  
Find the top 20 patients who are not from Chicago or Boston, have Diabetes or Hypertension, have a completed High/Critical  
procedure above 5,000, a finalized report above severity 70, and total pending billing above 5,000.  
Query  
WITH ClinicalPatients AS  
(
SELECT DISTINCT  
Patient_ID  
FROM tblMainMaster_Advent_health  
WHERE Medical_History IN ('Diabetes', 'Hypertension')  
),  
ProcedurePatients AS  
(
SELECT  
Patient_ID,  
MAX(Procedure_Charges) AS Maximum_Procedure_Charge  
FROM tblProcedure_Scans  
WHERE Procedure_Status = 'Completed'  
AND Priority IN ('High', 'Critical')  
GROUP BY Patient_ID  
HAVING MAX(Procedure_Charges) > 5000  
),  
ReportPatients AS  
(
SELECT  
Patient_ID,  
MAX(Severity_Score) AS Maximum_Severity  
FROM tblReports_tests  
WHERE Report_Status = 'Finalized'  
GROUP BY Patient_ID  
HAVING MAX(Severity_Score) > 70  
),  
BillingPatients AS  
(
SELECT  
Patient_ID,  
SUM(Pending_Amount) AS Total_Pending  
FROM tblBilling_Summary  
GROUP BY Patient_ID  
HAVING SUM(Pending_Amount) > 5000  
)
SELECT TOP 20  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
P.City,  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
53  
M.Medical_History,  
M.Risk_Factors,  
PP.Maximum_Procedure_Charge,  
RP.Maximum_Severity,  
BP.Total_Pending  
FROM tblPatient_Infomation AS P  
INNER JOIN ClinicalPatients AS CP  
ON P.Patient_ID = CP.Patient_ID  
INNER JOIN tblMainMaster_Advent_health AS M  
ON P.Patient_ID = M.Patient_ID  
INNER JOIN ProcedurePatients AS PP  
ON P.Patient_ID = PP.Patient_ID  
INNER JOIN ReportPatients AS RP  
ON P.Patient_ID = RP.Patient_ID  
INNER JOIN BillingPatients AS BP  
ON P.Patient_ID = BP.Patient_ID  
WHERE P.City NOT IN ('Chicago', 'Boston')  
ORDER BY BP.Total_Pending DESC;  
________________________________________  
78. Find patients who satisfy ALL financial conditions  
Find  
Find patients who have more than two completed procedures, total procedure charges above 10,000, total billing above 30,000, total  
paid amount below 25,000, and total pending amount above 5,000. The patient must also have a finalized report.  
Query  
WITH ProcedureSummary AS  
(
SELECT  
Patient_ID,  
COUNT(DISTINCT [Procedure_ID ]) AS Procedure_Count,  
SUM(Procedure_Charges) AS Procedure_Total  
FROM tblProcedure_Scans  
WHERE Procedure_Status = 'Completed'  
GROUP BY Patient_ID  
),  
BillingSummary AS  
(
SELECT  
Patient_ID,  
SUM(Total_Amount) AS Billing_Total,  
SUM(Paid_Amount) AS Paid_Total,  
SUM(Pending_Amount) AS Pending_Total  
FROM tblBilling_Summary  
GROUP BY Patient_ID  
),  
ReportSummary AS  
(
SELECT  
Patient_ID,  
COUNT(*) AS Finalized_Reports,  
MAX(Severity_Score) AS Maximum_Severity  
FROM tblReports_tests  
WHERE Report_Status = 'Finalized'  
GROUP BY Patient_ID  
)
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
PS.Procedure_Count,  
PS.Procedure_Total,  
BS.Billing_Total,  
BS.Paid_Total,  
BS.Pending_Total,  
RS.Finalized_Reports,  
RS.Maximum_Severity  
FROM tblPatient_Infomation AS P  
INNER JOIN tblMainMaster_Advent_health AS M  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
ON P.Patient_ID = M.Patient_ID  
54  
INNER JOIN ProcedureSummary AS PS  
ON P.Patient_ID = PS.Patient_ID  
INNER JOIN BillingSummary AS BS  
ON P.Patient_ID = BS.Patient_ID  
INNER JOIN ReportSummary AS RS  
ON P.Patient_ID = RS.Patient_ID  
WHERE PS.Procedure_Count > 2  
AND PS.Procedure_Total > 10000  
AND BS.Billing_Total > 30000  
AND BS.Paid_Total < 25000  
AND BS.Pending_Total > 5000  
ORDER BY BS.Pending_Total DESC;  
________________________________________  
79. Find the most complex patient cases  
Find  
Find the top 20 patients who have at least three procedures, at least two finalized reports, at least one High or Critical  
priority procedure, maximum report severity above 70, and total pending billing above 5,000. Exclude patients with any Cancelled  
billing record.  
Query  
WITH PatientProcedures AS  
(
SELECT  
Patient_ID,  
COUNT(DISTINCT [Procedure_ID ]) AS Procedure_Count,  
SUM(  
CASE  
WHEN Priority IN ('High', 'Critical')  
THEN 1  
ELSE 0  
END  
) AS HighPriority_Procedures  
FROM tblProcedure_Scans  
GROUP BY Patient_ID  
HAVING COUNT(DISTINCT [Procedure_ID ]) >= 3  
),  
PatientReports AS  
(
SELECT  
Patient_ID,  
COUNT(*) AS Finalized_Report_Count,  
MAX(Severity_Score) AS Maximum_Severity  
FROM tblReports_tests  
WHERE Report_Status = 'Finalized'  
GROUP BY Patient_ID  
HAVING COUNT(*) >= 2  
AND MAX(Severity_Score) > 70  
),  
PatientBilling AS  
(
SELECT  
Patient_ID,  
SUM(Pending_Amount) AS Total_Pending  
FROM tblBilling_Summary  
GROUP BY Patient_ID  
HAVING SUM(Pending_Amount) > 5000  
)
SELECT TOP 20  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
P.City,  
M.Cardiologist,  
PP.Procedure_Count,  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
PP.HighPriority_Procedures,  
55  
PR.Finalized_Report_Count,  
PR.Maximum_Severity,  
PB.Total_Pending  
FROM tblPatient_Infomation AS P  
INNER JOIN tblMainMaster_Advent_health AS M  
ON P.Patient_ID = M.Patient_ID  
INNER JOIN PatientProcedures AS PP  
ON P.Patient_ID = PP.Patient_ID  
INNER JOIN PatientReports AS PR  
ON P.Patient_ID = PR.Patient_ID  
INNER JOIN PatientBilling AS PB  
ON P.Patient_ID = PB.Patient_ID  
WHERE PP.HighPriority_Procedures >= 1  
AND NOT EXISTS  
(
SELECT 1  
FROM tblBilling_Summary AS B  
WHERE B.Patient_ID = P.Patient_ID  
AND B.Billing_Status = 'Cancelled'  
)
ORDER BY  
PR.Maximum_Severity DESC,  
PB.Total_Pending DESC;  
1. UNION — Easy  
Find  
Find all patients who either have a completed procedure or have a finalized report. Return each Patient_ID only once.  
SELECT Patient_ID  
FROM tblProcedure_Scans  
WHERE Procedure_Status = 'Completed'  
UNION  
SELECT Patient_ID  
FROM tblReports_tests  
WHERE Report_Status = 'Finalized';  
2. UNION ALL — Easy  
Find  
Find all Patient_IDs appearing in either the procedure table or billing table, including duplicate occurrences.  
SELECT Patient_ID  
FROM tblProcedure_Scans  
UNION ALL  
SELECT Patient_ID  
FROM tblBilling_Summary;  
3. INTERSECT — Medium  
Find  
Find patients who have both a completed procedure and a finalized report.  
SELECT Patient_ID  
FROM tblProcedure_Scans  
WHERE Procedure_Status = 'Completed'  
INTERSECT  
SELECT Patient_ID  
FROM tblReports_tests  
WHERE Report_Status = 'Finalized';  
4. EXCEPT — Medium  
Find  
Find patients who have completed procedures but do not have any finalized report.  
SELECT Patient_ID  
FROM tblProcedure_Scans  
WHERE Procedure_Status = 'Completed'  
EXCEPT  
SELECT Patient_ID  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
56  
FROM tblReports_tests  
WHERE Report_Status = 'Finalized';  
5. Subquery + CTE + JOIN  
Find  
Find patients whose total completed-procedure charges are greater than the average completed-procedure charges per patient, and  
display their doctor and total billing.  
WITH PatientProcedure AS  
(
SELECT  
Patient_ID,  
SUM(Procedure_Charges) AS Total_Procedure_Charges  
FROM tblProcedure_Scans  
WHERE Procedure_Status = 'Completed'  
GROUP BY Patient_ID  
),  
AverageProcedure AS  
(
SELECT  
AVG(Total_Procedure_Charges) AS Average_Charges  
FROM PatientProcedure  
)
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
M.Cardiologist,  
PP.Total_Procedure_Charges,  
(
SELECT SUM(B.Total_Amount)  
FROM tblBilling_Summary AS B  
WHERE B.Patient_ID = P.Patient_ID  
) AS Total_Billing  
FROM tblPatient_Infomation AS P  
INNER JOIN tblMainMaster_Advent_health AS M  
ON P.Patient_ID = M.Patient_ID  
INNER JOIN PatientProcedure AS PP  
ON P.Patient_ID = PP.Patient_ID  
CROSS JOIN AverageProcedure AS AP  
WHERE PP.Total_Procedure_Charges > AP.Average_Charges  
ORDER BY PP.Total_Procedure_Charges DESC;  
6. ROW_NUMBER() + PARTITION BY  
Find  
Find the highest-charged completed procedure for every patient.  
WITH RankedProcedures AS  
(
SELECT  
Patient_ID,  
[Procedure_ID ],  
Procedure_Name,  
Procedure_Charges,  
Procedure_Status,  
ROW_NUMBER() OVER  
(
PARTITION BY Patient_ID  
ORDER BY Procedure_Charges DESC  
) AS RN  
FROM tblProcedure_Scans  
WHERE Procedure_Status = 'Completed'  
)
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
RP.[Procedure_ID ],  
RP.Procedure_Name,  
RP.Procedure_Charges  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
FROM tblPatient_Infomation AS P  
57  
INNER JOIN RankedProcedures AS RP  
ON P.Patient_ID = RP.Patient_ID  
WHERE RP.RN = 1  
ORDER BY RP.Procedure_Charges DESC;  
7. RANK() + DENSE_RANK()  
Find  
Rank doctors according to the total number of patients they have treated. Display the doctor, patient count, RANK and DENSE_RANK.  
WITH DoctorPatients AS  
(
SELECT  
M.Cardiologist,  
COUNT(DISTINCT M.Patient_ID) AS Patient_Count  
FROM tblMainMaster_Advent_health AS M  
GROUP BY M.Cardiologist  
)
SELECT  
D.Doctor_Name,  
DP.Patient_Count,  
RANK() OVER  
(
ORDER BY DP.Patient_Count DESC  
) AS Doctor_Rank,  
DENSE_RANK() OVER  
(
ORDER BY DP.Patient_Count DESC  
) AS Doctor_Dense_Rank  
FROM tblDoctor_Information AS D  
INNER JOIN DoctorPatients AS DP  
ON D.Doctor_Name = DP.Cardiologist  
ORDER BY Doctor_Rank;  
Finding  
9. LEAD() + CTE + Multiple JOINs  
Find  
Find patients whose current completed procedure charge is greater than the next procedure charge for the same patient. Display  
patient, doctor, current procedure and next procedure.  
WITH ProcedureSequence AS  
(
SELECT  
Patient_ID,  
[Procedure_ID ],  
Procedure_Name,  
Procedure_Charges,  
Procedure_Date,  
LEAD(Procedure_Charges) OVER  
(
PARTITION BY Patient_ID  
ORDER BY Procedure_Date  
) AS Next_Procedure_Charge  
FROM tblProcedure_Scans  
WHERE Procedure_Status = 'Completed'  
)
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
M.Cardiologist,  
PS.Procedure_Name,  
PS.Procedure_Date,  
PS.Procedure_Charges,  
PS.Next_Procedure_Charge  
FROM tblPatient_Infomation AS P  
INNER JOIN ProcedureSequence AS PS  
ON P.Patient_ID = PS.Patient_ID  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
58  
INNER JOIN tblMainMaster_Advent_health AS M  
ON P.Patient_ID = M.Patient_ID  
INNER JOIN tblDoctor_Information AS D  
ON M.Cardiologist = D.Doctor_Name  
WHERE PS.Next_Procedure_Charge IS NOT NULL  
AND PS.Procedure_Charges > PS.Next_Procedure_Charge  
ORDER BY  
P.Patient_ID,  
PS.Procedure_Date;  
10. MASTER — CTE + SET OPERATORS + SUBQUERY + WINDOWS  
Find  
Find the top 20 patients who have both completed procedures and finalized reports, but are not among patients with cancelled  
billing. For each patient, calculate total completed-procedure charges, maximum report severity and total pending billing. Rank  
patients within each doctors patient group based on total procedure charges. Display only the top 3 patients under each doctor.  
WITH  
/* =====================================================  
1. PATIENTS WITH COMPLETED PROCEDURES  
===================================================== */  
CompletedPatients AS  
(
SELECT DISTINCT  
Patient_ID  
FROM tblProcedure_Scans  
WHERE Procedure_Status = 'Completed'  
),  
/* =====================================================  
2. PATIENTS WITH FINALIZED REPORTS  
===================================================== */  
FinalizedPatients AS  
(
SELECT DISTINCT  
Patient_ID  
FROM tblReports_tests  
WHERE Report_Status = 'Finalized'  
),  
/* =====================================================  
3. PATIENTS HAVING BOTH  
===================================================== */  
EligiblePatients AS  
(
SELECT Patient_ID  
FROM CompletedPatients  
INTERSECT  
SELECT Patient_ID  
FROM FinalizedPatients  
EXCEPT  
SELECT DISTINCT Patient_ID  
FROM tblBilling_Summary  
WHERE Billing_Status = 'Cancelled'  
),  
/* =====================================================  
4. PATIENT-LEVEL SUMMARY  
===================================================== */  
PatientSummary AS  
(
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
M.Cardiologist,  
SUM(PR.Procedure_Charges) AS Total_Procedure_Charges,  
MAX(R.Severity_Score) AS Maximum_Severity,  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
SUM(B.Pending_Amount) AS Total_Pending  
59  
FROM tblPatient_Infomation AS P  
INNER JOIN EligiblePatients AS EP  
ON P.Patient_ID = EP.Patient_ID  
INNER JOIN tblMainMaster_Advent_health AS M  
ON P.Patient_ID = M.Patient_ID  
INNER JOIN tblProcedure_Scans AS PR  
ON P.Patient_ID = PR.Patient_ID  
INNER JOIN tblReports_tests AS R  
ON P.Patient_ID = R.Patient_ID  
INNER JOIN tblBilling_Summary AS B  
ON P.Patient_ID = B.Patient_ID  
WHERE PR.Procedure_Status = 'Completed'  
AND R.Report_Status = 'Finalized'  
GROUP BY  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
M.Cardiologist  
),  
/* =====================================================  
5. RANK PATIENTS WITHIN EACH DOCTOR  
===================================================== */  
RankedPatients AS  
(
SELECT  
*,  
ROW_NUMBER() OVER  
(
PARTITION BY Cardiologist  
ORDER BY Total_Procedure_Charges DESC  
) AS Doctor_Patient_Rank  
FROM PatientSummary  
),  
/* =====================================================  
6. OVERALL TOP PATIENTS  
===================================================== */  
FinalPatients AS  
(
SELECT TOP 20  
*
FROM RankedPatients  
WHERE Doctor_Patient_Rank <= 3  
ORDER BY  
Total_Procedure_Charges DESC  
)
SELECT  
Patient_ID,  
First_Name,  
Last_Name,  
Cardiologist,  
Total_Procedure_Charges,  
Maximum_Severity,  
Total_Pending,  
Doctor_Patient_Rank  
FROM FinalPatients  
ORDER BY  
Total_Procedure_Charges DESC;  
1. String Functions — Easy  
Find  
Find all patients whose full name is displayed in uppercase along with the length of their full name.  
SELECT  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
Patient_ID,  
60  
UPPER(  
CONCAT(First_Name, ' ', Last_Name)  
) AS Full_Name,  
LEN(  
CONCAT(First_Name, ' ', Last_Name)  
) AS Name_Length  
FROM tblPatient_Infomation  
ORDER BY Name_Length DESC;  
2. String Functions — Medium  
Find  
Find patients whose last name starts with S and display their last name in uppercase, first name in lowercase, and the first three  
characters of their last name.  
SELECT  
Patient_ID,  
LOWER(First_Name) AS First_Name,  
UPPER(Last_Name) AS Last_Name,  
LEFT(Last_Name, 3) AS Last_Name_Prefix  
FROM tblPatient_Infomation  
WHERE Last_Name LIKE 'S%'  
ORDER BY Last_Name;  
3. Date & Time Functions  
Find  
Find all procedures performed during the year 2025 and display the procedure date, year, month and month name.  
SELECT  
Patient_ID,  
[Procedure_ID ],  
Procedure_Name,  
Procedure_Date,  
YEAR(Procedure_Date) AS Procedure_Year,  
MONTH(Procedure_Date) AS Procedure_Month,  
DATENAME(MONTH, Procedure_Date) AS Month_Name  
FROM tblProcedure_Scans  
WHERE Procedure_Date >= '2025-01-01'  
AND Procedure_Date < '2026-01-01'  
ORDER BY Procedure_Date;  
4. Date Functions + DATEDIFF  
Find  
Find patients whose first procedure occurred more than 30 days after their registration date. Display the number of days between  
registration and first procedure.  
WITH FirstProcedure AS  
(
SELECT  
Patient_ID,  
MIN(Procedure_Date) AS First_Procedure_Date  
FROM tblProcedure_Scans  
GROUP BY Patient_ID  
)
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
P.Registration_Date,  
FP.First_Procedure_Date,  
DATEDIFF(  
DAY,  
P.Registration_Date,  
FP.First_Procedure_Date  
) AS Days_To_First_Procedure  
FROM tblPatient_Infomation AS P  
INNER JOIN FirstProcedure AS FP  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
ON P.Patient_ID = FP.Patient_ID  
61  
WHERE DATEDIFF(  
DAY,  
P.Registration_Date,  
FP.First_Procedure_Date  
) > 30  
ORDER BY Days_To_First_Procedure DESC;  
5. Numeric Functions  
Find  
Find procedures where the charge is above 5,000 and display the original charge, rounded charge, ceiling value and floor value.  
SELECT  
Patient_ID,  
[Procedure_ID ],  
Procedure_Name,  
Procedure_Charges AS Original_Charge,  
ROUND(Procedure_Charges, 0) AS Rounded_Charge,  
CEILING(Procedure_Charges) AS Ceiling_Charge,  
FLOOR(Procedure_Charges) AS Floor_Charge  
FROM tblProcedure_Scans  
WHERE Procedure_Charges > 5000  
ORDER BY Procedure_Charges DESC;  
7. Conditional Functions — CASE  
Find  
Classify patients according to their age: Under 18 = Minor, 18–40 = Young Adult, 41–60 = Middle Age, above 60 = Senior.  
SELECT  
Patient_ID,  
First_Name,  
Last_Name,  
Age,  
CASE  
WHEN Age < 18  
THEN 'Minor'  
WHEN Age BETWEEN 18 AND 40  
THEN 'Young Adult'  
WHEN Age BETWEEN 41 AND 60  
THEN 'Middle Age'  
WHEN Age > 60  
THEN 'Senior'  
ELSE 'Unknown'  
END AS Age_Category  
FROM tblPatient_Infomation  
ORDER BY Age;  
----------------------------------------------------------------------------------------------------------------------------------  
-------------------------------------------------------------------  
â•‘ LEVEL 8 — SQL SERVER PROGRAMMING  
1. Variable — Calculate a Date Range  
Find  
Declare a variable containing the year 2025 and display the start date and end date of that year.  
DECLARE @Year INT = 2025;  
DECLARE @StartDate DATE =  
DATEFROMPARTS(@Year, 1, 1);  
DECLARE @EndDate DATE =  
DATEFROMPARTS(@Year, 12, 31);  
SELECT  
@Year AS Selected_Year,  
@StartDate AS Start_Date,  
@EndDate AS End_Date;  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
62  
2. Variable + SELECT  
Find  
Store the total number of patients in a variable and display a message based on the patient count.  
DECLARE @PatientCount INT;  
SELECT  
@PatientCount = COUNT(*)  
FROM tblPatient_Infomation;  
SELECT  
@PatientCount AS Total_Patients;  
3. IF / ELSE — Patient Count  
Find  
Check whether the database contains more than 10,000 patients. Display Large Patient Dataset if true; otherwise display Small  
Patient Dataset.  
DECLARE @PatientCount INT;  
SELECT  
@PatientCount = COUNT(*)  
FROM tblPatient_Infomation;  
IF @PatientCount > 10000  
BEGIN  
PRINT 'Large Patient Dataset';  
END  
ELSE  
BEGIN  
PRINT 'Small Patient Dataset';  
END;  
4. IF / ELSE — Date Range  
Find  
Check whether there are any procedures performed during 2025.  
DECLARE @ProcedureCount INT;  
SELECT  
@ProcedureCount = COUNT(*)  
FROM tblProcedure_Scans  
WHERE Procedure_Date >= '2025-01-01'  
AND Procedure_Date < '2026-01-01';  
IF @ProcedureCount > 0  
BEGIN  
PRINT 'Procedures found for 2025';  
END  
ELSE  
BEGIN  
PRINT 'No procedures found for 2025';  
END;  
5. IF / ELSE IF / ELSE  
Find  
Calculate the average procedure charge and classify the dataset as High Cost, Medium Cost or Low Cost.  
DECLARE @AverageCharge DECIMAL(18,2);  
SELECT  
@AverageCharge = AVG(Procedure_Charges)  
FROM tblProcedure_Scans;  
IF @AverageCharge >= 10000  
BEGIN  
PRINT 'High Cost Dataset';  
END  
ELSE IF @AverageCharge >= 5000  
BEGIN  
PRINT 'Medium Cost Dataset';  
END  
ELSE  
BEGIN  
PRINT 'Low Cost Dataset';  
END;  
SELECT  
@AverageCharge AS Average_Procedure_Charge;  
EASY → MEDIUM — 6 to 10  
6. WHILE — Generate Years  
Find  
Use a WHILE loop to display every year from 2020 through 2029.  
DECLARE @Year INT = 2020;  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
63  
WHILE @Year <= 2029  
BEGIN  
PRINT CONCAT('Year: ', @Year);  
SET @Year = @Year + 1;  
END;  
Finding  
Basic loop:  
2020  
↓
2021  
↓
2022  
↓
...  
↓
2029  
________________________________________  
7. WHILE + Date Range  
Find  
Use a WHILE loop to count the number of procedures performed in every year from 2020 to 2029.  
DECLARE @Year INT = 2020;  
DECLARE @StartDate DATE;  
DECLARE @EndDate DATE;  
WHILE @Year <= 2029  
BEGIN  
SET @StartDate =  
DATEFROMPARTS(@Year, 1, 1);  
SET @EndDate =  
DATEFROMPARTS(@Year + 1, 1, 1);  
SELECT  
@Year AS Procedure_Year,  
COUNT(*) AS Procedure_Count  
FROM tblProcedure_Scans  
WHERE Procedure_Date >= @StartDate  
AND Procedure_Date < @EndDate;  
SET @Year = @Year + 1;  
END;  
Finding  
This teaches:  
WHILE  
+
Variables  
+
DATEFROMPARTS  
+
COUNT  
________________________________________  
8. Temporary Table  
Find  
Create a temporary table containing total procedure charges per patient and display patients whose total is greater than 10,000.  
CREATE TABLE #PatientProcedureSummary  
(
Patient_ID INT,  
Total_Procedure_Charges DECIMAL(18,2)  
);  
INSERT INTO #PatientProcedureSummary  
(
Patient_ID,  
Total_Procedure_Charges  
)
SELECT  
Patient_ID,  
SUM(Procedure_Charges)  
FROM tblProcedure_Scans  
GROUP BY Patient_ID;  
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
T.Total_Procedure_Charges  
FROM tblPatient_Infomation AS P  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
64  
INNER JOIN #PatientProcedureSummary AS T  
ON P.Patient_ID = T.Patient_ID  
WHERE T.Total_Procedure_Charges > 10000  
ORDER BY T.Total_Procedure_Charges DESC;  
DROP TABLE #PatientProcedureSummary;  
Finding  
Programming flow:  
Create #Temp  
↓
INSERT data  
↓
JOIN  
↓
Filter  
↓
DROP  
________________________________________  
9. Table Variable  
Find  
Store patients who had completed procedures during 2025 in a table variable and display their procedure count.  
DECLARE @Patients2025 TABLE  
(
Patient_ID INT  
);  
INSERT INTO @Patients2025  
(
Patient_ID  
)
SELECT DISTINCT  
Patient_ID  
FROM tblProcedure_Scans  
WHERE Procedure_Status = 'Completed'  
AND Procedure_Date >= '2025-01-01'  
AND Procedure_Date < '2026-01-01';  
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
COUNT(PR.[Procedure_ID ]) AS Procedure_Count  
FROM @Patients2025 AS X  
INNER JOIN tblPatient_Infomation AS P  
ON X.Patient_ID = P.Patient_ID  
INNER JOIN tblProcedure_Scans AS PR  
ON X.Patient_ID = PR.Patient_ID  
WHERE PR.Procedure_Status = 'Completed'  
AND PR.Procedure_Date >= '2025-01-01'  
AND PR.Procedure_Date < '2026-01-01'  
GROUP BY  
P.Patient_ID,  
P.First_Name,  
P.Last_Name  
ORDER BY Procedure_Count DESC;  
Finding  
Difference to remember:  
#TempTable  
vs  
@TableVariable  
Both can temporarily hold data, but they have different SQL Server behavior and optimization characteristics.  
________________________________________  
10. Temporary Table + Multiple JOINs  
Find  
Create a temporary table containing patients whose completed procedure charges exceed 20,000, then join it with patient, doctor  
and billing information.  
CREATE TABLE #HighValuePatients  
(
Patient_ID INT,  
Total_Procedure_Charges DECIMAL(18,2)  
);  
INSERT INTO #HighValuePatients  
SELECT  
Patient_ID,  
SUM(Procedure_Charges)  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
65  
FROM tblProcedure_Scans  
WHERE Procedure_Status = 'Completed'  
GROUP BY Patient_ID  
HAVING SUM(Procedure_Charges) > 20000;  
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
M.Cardiologist,  
H.Total_Procedure_Charges,  
SUM(ISNULL(B.Total_Amount, 0)) AS Total_Billing  
FROM #HighValuePatients AS H  
INNER JOIN tblPatient_Infomation AS P  
ON H.Patient_ID = P.Patient_ID  
INNER JOIN tblMainMaster_Advent_health AS M  
ON P.Patient_ID = M.Patient_ID  
LEFT JOIN tblBilling_Summary AS B  
ON P.Patient_ID = B.Patient_ID  
GROUP BY  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
M.Cardiologist,  
H.Total_Procedure_Charges  
ORDER BY H.Total_Procedure_Charges DESC;  
DROP TABLE #HighValuePatients;  
________________________________________  
MEDIUM — 11 to 15  
11. Stored Procedure — Basic  
Find  
Create a stored procedure that accepts a year and returns all completed procedures performed during that year.  
CREATE OR ALTER PROCEDURE usp_GetProceduresByYear  
@Year INT  
AS  
BEGIN  
SET NOCOUNT ON;  
SELECT  
Patient_ID,  
[Procedure_ID ],  
Procedure_Name,  
Procedure_Date,  
Procedure_Charges  
FROM tblProcedure_Scans  
WHERE Procedure_Status = 'Completed'  
AND Procedure_Date >= DATEFROMPARTS(@Year, 1, 1)  
AND Procedure_Date < DATEFROMPARTS(@Year + 1, 1, 1)  
ORDER BY Procedure_Date;  
END;  
Execute  
EXEC usp_GetProceduresByYear  
@Year = 2025;  
________________________________________  
12. Stored Procedure — Patient Summary  
Find  
Create a stored procedure that accepts a Patient_ID and returns the patients details, procedure count, total procedure charges and  
total billing.  
CREATE OR ALTER PROCEDURE usp_GetPatientSummary  
@Patient_ID INT  
AS  
BEGIN  
SET NOCOUNT ON;  
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
COUNT(DISTINCT PR.[Procedure_ID ]) AS Procedure_Count,  
66  
SUM(ISNULL(PR.Procedure_Charges, 0))  
AS Total_Procedure_Charges,  
SUM(ISNULL(B.Total_Amount, 0))  
AS Total_Billing  
FROM tblPatient_Infomation AS P  
LEFT JOIN tblProcedure_Scans AS PR  
ON P.Patient_ID = PR.Patient_ID  
LEFT JOIN tblBilling_Summary AS B  
ON P.Patient_ID = B.Patient_ID  
WHERE P.Patient_ID = @Patient_ID  
GROUP BY  
P.Patient_ID,  
P.First_Name,  
P.Last_Name;  
END;  
Execute  
EXEC usp_GetPatientSummary  
@Patient_ID = 1005;  
________________________________________  
13. Stored Procedure + IF / ELSE  
Find  
Create a procedure that accepts a patient ID. If the patient exists, return their procedure summary; otherwise display Patient Not  
Found.  
CREATE OR ALTER PROCEDURE usp_CheckPatient  
@Patient_ID INT  
AS  
BEGIN  
SET NOCOUNT ON;  
DECLARE @PatientCount INT;  
SELECT  
@PatientCount = COUNT(*)  
FROM tblPatient_Infomation  
WHERE Patient_ID = @Patient_ID;  
IF @PatientCount = 0  
BEGIN  
PRINT 'Patient Not Found';  
END  
ELSE  
BEGIN  
SELECT  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
COUNT(PR.[Procedure_ID ]) AS Procedure_Count,  
SUM(ISNULL(PR.Procedure_Charges, 0))  
AS Total_Procedure_Charges  
FROM tblPatient_Infomation AS P  
LEFT JOIN tblProcedure_Scans AS PR  
ON P.Patient_ID = PR.Patient_ID  
WHERE P.Patient_ID = @Patient_ID  
GROUP BY  
P.Patient_ID,  
P.First_Name,  
P.Last_Name;  
END;  
END;  
________________________________________  
14. User-Defined Function — Age Category  
Find  
Create a scalar function that accepts an age and returns the appropriate age category.  
CREATE OR ALTER FUNCTION dbo.fn_AgeCategory  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
67  
(
@Age INT  
)
RETURNS VARCHAR(30)  
AS  
BEGIN  
DECLARE @Category VARCHAR(30);  
SET @Category =  
CASE  
WHEN @Age < 18  
THEN 'Minor'  
WHEN @Age BETWEEN 18 AND 40  
THEN 'Young Adult'  
WHEN @Age BETWEEN 41 AND 60  
THEN 'Middle Age'  
WHEN @Age > 60  
THEN 'Senior'  
ELSE 'Unknown'  
END;  
RETURN @Category;  
END;  
15. User-Defined Function + Date  
Find  
Create a function that accepts a patients registration date and returns the number of complete years the patient has been  
registered as of 31 December 2029.  
CREATE OR ALTER FUNCTION dbo.fn_RegistrationYears  
(
@RegistrationDate DATE  
)
RETURNS INT  
AS  
BEGIN  
DECLARE @Years INT;  
SET @Years =  
DATEDIFF(  
YEAR,  
@RegistrationDate,  
'2029-12-31'  
);  
IF DATEADD(  
YEAR,  
@Years,  
@RegistrationDate  
) > '2029-12-31'  
BEGIN  
SET @Years = @Years - 1;  
END;  
RETURN @Years;  
END;  
Use it  
SELECT  
Patient_ID,  
First_Name,  
Last_Name,  
Registration_Date,  
dbo.fn_RegistrationYears(  
Registration_Date  
) AS Registration_Years  
FROM tblPatient_Infomation;  
________________________________________  
  HARD — 16 to 20  
16. Error Handling — TRY / CATCH  
Find  
Create a procedure that inserts a billing record and handles errors using TRY...CATCH. If the insert fails, return the error  
message.  
CREATE OR ALTER PROCEDURE usp_InsertBilling  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
68  
@Patient_ID INT,  
@Total_Amount DECIMAL(18,2),  
@Paid_Amount DECIMAL(18,2),  
@Pending_Amount DECIMAL(18,2)  
AS  
BEGIN  
SET NOCOUNT ON;  
BEGIN TRY  
INSERT INTO tblBilling_Summary  
(
Patient_ID,  
Total_Amount,  
Paid_Amount,  
Pending_Amount  
)
VALUES  
(
@Patient_ID,  
@Total_Amount,  
@Paid_Amount,  
@Pending_Amount  
);  
PRINT 'Billing record inserted successfully';  
END TRY  
BEGIN CATCH  
SELECT  
ERROR_NUMBER() AS Error_Number,  
ERROR_MESSAGE() AS Error_Message,  
ERROR_LINE() AS Error_Line,  
ERROR_PROCEDURE() AS Error_Procedure;  
END CATCH;  
END;  
Finding  
The structure is:  
BEGIN TRY  
↓
Execute  
↓
Success  
OR  
↓
BEGIN CATCH  
↓
Error information  
________________________________________  
17. Error Handling + Transaction  
Find  
Create a procedure that inserts a procedure record and billing record together. If either operation fails, roll back the entire  
transaction.  
CREATE OR ALTER PROCEDURE usp_AddProcedureAndBilling  
@Patient_ID INT,  
@Procedure_Name VARCHAR(100),  
@Procedure_Charges DECIMAL(18,2),  
@Total_Amount DECIMAL(18,2)  
AS  
BEGIN  
SET NOCOUNT ON;  
BEGIN TRY  
BEGIN TRANSACTION;  
INSERT INTO tblProcedure_Scans  
(
Patient_ID,  
Procedure_Name,  
Procedure_Charges  
)
VALUES  
(
@Patient_ID,  
@Procedure_Name,  
@Procedure_Charges  
);  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
69  
INSERT INTO tblBilling_Summary  
(
Patient_ID,  
Total_Amount  
)
VALUES  
(
@Patient_ID,  
@Total_Amount  
);  
COMMIT TRANSACTION;  
PRINT 'Transaction completed successfully';  
END TRY  
BEGIN CATCH  
IF @@TRANCOUNT > 0  
BEGIN  
ROLLBACK TRANSACTION;  
END;  
SELECT  
ERROR_NUMBER() AS Error_Number,  
ERROR_MESSAGE() AS Error_Message;  
END CATCH;  
END;  
Finding  
This introduces:  
TRANSACTION  
↓
INSERT 1  
↓
INSERT 2  
↓
COMMIT  
If error:  
↓
ROLLBACK  
________________________________________  
18. Dynamic SQL  
Find  
Create a stored procedure that accepts a table name and dynamically returns the first 20 records from that table.  
CREATE OR ALTER PROCEDURE usp_GetTopRecords  
@TableName SYSNAME  
AS  
BEGIN  
SET NOCOUNT ON;  
DECLARE @SQL NVARCHAR(MAX);  
SET @SQL =  
N'SELECT TOP 20 * FROM '  
+ QUOTENAME(@TableName);  
EXEC sp_executesql @SQL;  
END;  
Execute  
EXEC usp_GetTopRecords  
@TableName = 'tblPatient_Infomation';  
Finding  
The SQL statement is constructed dynamically:  
@TableName  
↓
Dynamic SQL  
↓
sp_executesql  
Important  
QUOTENAME() is important when dynamically inserting object names into SQL.  
________________________________________  
19. Dynamic SQL + Date Range + Parameters  
Find  
Create a stored procedure that dynamically filters a selected table by a date column between two supplied dates, while keeping the  
actual date values parameterized.  
CREATE OR ALTER PROCEDURE usp_GetRecordsByDate  
@TableName SYSNAME,  
@DateColumn SYSNAME,  
@StartDate DATE,  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
70  
@EndDate DATE  
AS  
BEGIN  
SET NOCOUNT ON;  
DECLARE @SQL NVARCHAR(MAX);  
SET @SQL =  
N'SELECT *  
FROM ' + QUOTENAME(@TableName) +  
N' WHERE ' + QUOTENAME(@DateColumn) +  
N' >= @StartDate  
AND ' + QUOTENAME(@DateColumn) +  
N' < @EndDate';  
EXEC sp_executesql  
@SQL,  
N'@StartDate DATE, @EndDate DATE',  
@StartDate = @StartDate,  
@EndDate = @EndDate;  
END;  
Execute  
EXEC usp_GetRecordsByDate  
@TableName = 'tblProcedure_Scans',  
@DateColumn = 'Procedure_Date',  
@StartDate = '2025-01-01',  
@EndDate = '2026-01-01';  
Finding  
This is an important distinction:  
Object names  
↓
QUOTENAME()  
Data values  
↓
sp_executesql parameters  
Do not concatenate date values directly into dynamic SQL when they can be parameters.  
________________________________________  
  20. MASTER QUESTION — Everything Combined  
Find  
Create a stored procedure that accepts a year between 2020 and 2029 and generates a patient summary for that year. The procedure  
should:  
1. Validate that the year is between 2020 and 2029.  
2. Use variables for the date range.  
3. Use a temporary table to store yearly patient procedure totals.  
4. Include only completed procedures.  
5. Join patient, doctor and billing information.  
6. Calculate total procedures and total procedure charges.  
7. Calculate total paid and pending amounts.  
8. Categorize patients based on their total procedure charges.  
9. Use TRY...CATCH for error handling.  
10. Return the top 20 patients by procedure charges.  
________________________________________  
Solution  
CREATE OR ALTER PROCEDURE usp_YearlyPatientAnalysis  
@Year INT  
AS  
BEGIN  
SET NOCOUNT ON;  
/* =====================================================  
1. ERROR HANDLING  
===================================================== */  
BEGIN TRY  
/* =================================================  
2. VALIDATE YEAR  
================================================= */  
IF @Year < 2020 OR @Year > 2029  
BEGIN  
THROW  
50001,  
'Year must be between 2020 and 2029.',  
1;  
END;  
/* =================================================  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
71  
3. DECLARE DATE VARIABLES  
================================================= */  
DECLARE @StartDate DATE;  
DECLARE @EndDate DATE;  
SET @StartDate =  
DATEFROMPARTS(@Year, 1, 1);  
SET @EndDate =  
DATEFROMPARTS(@Year + 1, 1, 1);  
/* =================================================  
4. TEMPORARY TABLE  
================================================= */  
CREATE TABLE #YearlyPatientSummary  
(
Patient_ID INT,  
Procedure_Count INT,  
Total_Procedure_Charges DECIMAL(18,2)  
);  
/* =================================================  
5. INSERT YEARLY PROCEDURE DATA  
================================================= */  
INSERT INTO #YearlyPatientSummary  
(
Patient_ID,  
Procedure_Count,  
Total_Procedure_Charges  
)
SELECT  
Patient_ID,  
COUNT(*) AS Procedure_Count,  
SUM(Procedure_Charges)  
AS Total_Procedure_Charges  
FROM tblProcedure_Scans  
WHERE Procedure_Status = 'Completed'  
AND Procedure_Date >= @StartDate  
AND Procedure_Date < @EndDate  
GROUP BY  
Patient_ID;  
/* =================================================  
6. FINAL PATIENT ANALYSIS  
================================================= */  
SELECT TOP 20  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
M.Cardiologist,  
@Year AS Analysis_Year,  
T.Procedure_Count,  
T.Total_Procedure_Charges,  
/* =============================================  
BILLING  
============================================= */  
SUM(  
ISNULL(B.Paid_Amount, 0)  
) AS Total_Paid,  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
72  
SUM(  
ISNULL(B.Pending_Amount, 0)  
) AS Total_Pending,  
/* =============================================  
PATIENT CATEGORY  
============================================= */  
CASE  
WHEN T.Total_Procedure_Charges >= 20000  
THEN 'High Value Patient'  
WHEN T.Total_Procedure_Charges >= 10000  
THEN 'Medium Value Patient'  
ELSE 'Low Value Patient'  
END AS Patient_Category  
FROM #YearlyPatientSummary AS T  
/* =============================================  
PATIENT  
============================================= */  
INNER JOIN tblPatient_Infomation AS P  
ON T.Patient_ID = P.Patient_ID  
/* =============================================  
DOCTOR  
============================================= */  
INNER JOIN tblMainMaster_Advent_health AS M  
ON P.Patient_ID = M.Patient_ID  
/* =============================================  
BILLING  
============================================= */  
LEFT JOIN tblBilling_Summary AS B  
ON P.Patient_ID = B.Patient_ID  
GROUP BY  
P.Patient_ID,  
P.First_Name,  
P.Last_Name,  
M.Cardiologist,  
T.Procedure_Count,  
T.Total_Procedure_Charges  
ORDER BY  
T.Total_Procedure_Charges DESC;  
/* =================================================  
7. CLEANUP  
================================================= */  
DROP TABLE #YearlyPatientSummary;  
END TRY  
/* =====================================================  
8. ERROR HANDLING  
===================================================== */  
C:\Users\Enzymes Technology\Desktop\SQLQuery4.sql  
BEGIN CATCH  
73  
IF OBJECT_ID(  
'tempdb..#YearlyPatientSummary'  
) IS NOT NULL  
BEGIN  
DROP TABLE #YearlyPatientSummary;  
END;  
SELECT  
ERROR_NUMBER() AS Error_Number,  
ERROR_MESSAGE() AS Error_Message,  
ERROR_LINE() AS Error_Line,  
ERROR_PROCEDURE() AS Error_Procedure;  
END CATCH;  
END;